March 23, 2016 at 8:40 am
Thank you, Grant, for your valuable input!
I also very much enjoyed your book on execution plans, which I partially read in the recent past.
Likes to play Chess
March 23, 2016 at 8:43 am
Happy to help out. I'm rewriting the execution plans book. It should be done soon I hope.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 11:18 am
Grant Fritchey (3/23/2016)
Happy to help out. I'm rewriting the execution plans book. It should be done soon I hope.
I finished your execution plan book while I was on vacation last week. Very good stuff sir! I made mention a few posts back of how the optimizer can choose an index from an indexed view without the view being referenced (note the two queries and resulting plan here). That's one of the many cool things I learned from your book. I look forward to the new one.
-- Itzik Ben-Gan 2001
March 23, 2016 at 12:40 pm
VoldemarG (3/22/2016)
But even with using your metod, it means you need to go index by index? Not all at once as a result of 1 or 2 statements run?That appears impossible..
Do you know how to write SQL? Do you know how to use SQL cursors? Can you combine 2 queries into 1? Or is it also an impossible task? You need to work on that lazy attitude of yours if you want to achieve any results in optimizing SQL queries. Because it requires much more time an effort than you can imagine in order to find which indexes should be created.
And even if you get a comprehensive list of hundreds of missing indexes and corresponding queries then what are you going to do with that? Do you really need all of them at once? Or you can simply start from the most expensive ones? Because you still need to go index by index and plan by plan in order to analyze them and decide what indexes should be created or in some cases how the queries can be re-written to run faster. There is no other way to do it. No magic formula to tell you the right index, only scripts to help you to make your own decision.
Extracting the whole cache (which is gigabytes of data) + missing index DMV, saving them on another server and running parse for 40 minutes doesn't sound like a good idea. The efficiency of this process is close to zero. Not to mention the fact that the results may become obsolete by the time you actually get them.
March 24, 2016 at 4:33 am
Alexander Suprun (3/23/2016)
Extracting the whole cache (which is gigabytes of data) + missing index DMV, saving them on another server and running parse for 40 minutes doesn't sound like a good idea. The efficiency of this process is close to zero. Not to mention the fact that the results may become obsolete by the time you actually get them.
I don't agree with this. It's actually a good approach for dealing with the fact that you don't want to run the XPath queries against your production server. They're very resource intensive. Moving them off the prod server is a good idea and this is how you would do that. Further, the query patterns on most servers are pretty concrete from day to day, even week to week. So taking a day or so to identify those patterns and finding tuning opportunities by combining the ability to pull the top 10 most frequently called queries with the suggestions of missing indexes, isn't a bad approach at all.
At the end, you're still going to have to approach each index separately, evaluate it, test it, and implement it. But using the tools at hand to quickly narrow down the list is how I'd get it done and I don't think it's at all wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply