July 7, 2010 at 12:48 am
Hi,
Is it a good idea to blindly apply all the indexes suggested by dm_db_missing_index_details.
To put it in other way, does dm_db_missing_index_details suggest you all the indexes that you database needs.
Thanks and Regards,
Muzammil Ahmed
July 7, 2010 at 1:12 am
This was removed by the editor as SPAM
July 7, 2010 at 1:25 am
You should take the recommendation under consideration, but don’t fallow them blindly. As stewartc Already mentioned, it shows the recommendation per query that was used on the server. Many times you can see that it recommends a number of indexes that are almost identical (for example minor differences in the include columns). In those cases there is a good chance that one index will be beneficial. Also there is no reason to create an index for each SQL statement that was used on the database. The DM view will show you recommendation for every single SQL query that was used regardless of how many times it was used. Another problem is that if a table has no index at all, and the server used the trivial query plan, you won’t see any data about it in the view. In short – this DM is a nice tool, but it can’t replace the DBA:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 6:20 am
muzahm (7/7/2010)
Hi,Is it a good idea to blindly apply all the indexes suggested by dm_db_missing_index_details.
To put it in other way, does dm_db_missing_index_details suggest you all the indexes that you database needs.
Thanks and Regards,
Muzammil Ahmed
Absolutely not. I've seen way too many instances of nearly identical indexes being suggested, or bad indexes being suggested. The missing index information is a very useful starting point for tuning, but it can not be taken completely as written. You must carefully examine it's recommendations and, most important, test them, to ensure those indexes are actually helpful.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply