July 9, 2012 at 9:22 pm
Comments posted to this topic are about the item Issue with Current Merge Join algorithm and ways to Improve it
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 10, 2012 at 7:14 am
What happens when you index?
Is there a Connect Article that list this issue?
July 11, 2012 at 12:54 am
What happens when you index?
Sorry but I did not get what do you mean by create index on what or which column.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 11, 2012 at 6:21 am
It would depend on the recomendations of performance tuner or missing indexes dmv.
Poor query performance caused by extra seeks are historicly resolved by better indexing, primary key selections, and Foreign key relationships.
Bettter Primary keys, CLUSTERED and NON CLUSTERED indexes are solutions.
I did not notice any detail about this.
Your post does list it in the second sript as a
Work Around
. 😀
Also, I do not see the script that shows how to recreated you issue in the area of you post where you mention it. Just a
/* */
so it is difficult to understand exactly what we are resolving.
July 11, 2012 at 7:14 am
It is not related to index. This is the case when your best index is used to get the data specially from Inner table for merge join. The issue is where to start the scan of the index. By default it is at the start of the index and I am showing in my script that for some of queries you can tell you the optimizer where to start that scan by adding extra logic. I can see the full script.Not sure why are you not able to.
Try below link
http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 11, 2012 at 8:04 am
Gullimeel (7/11/2012)
It is not related to index. This is the case when your best index is used to get the data specially from Inner table for merge join.
I Now that I have link to your entire recreation code maybe I can test your scenario and see what you are talking about.
Although at first glance it looks like something I, as a DBA, would run a few times and then check Missing Indexes DMV. Something that I would do for any query Joining data that showed a performance issue when seeking the scan key.
I see where what you are pointing out happens. Just not how this is something unique to how MERGE JOIN is calculated by optimizer. 😀
May 10, 2016 at 9:18 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply