October 3, 2013 at 9:02 am
So I had come across a query (don't recall where) which uses four missing index DMVs to give an idea as to where the SQL engine thinks an index might help. Now, I'm not blindly following it's suggestions (as of now I've used its recommendations to add ONE index to replace an existing index,) but I'd like to get an idea of what others think.
The columns I'm paying the most attention to when looking for possible improvements are:
sys.dm_db_missing_index_group_stats
User_seeks
User_scans
avg_total_user_cost
So far, my feeling is, if the seeks and scans are low, then don't add the index regardless of what the user_cost is.
Does anyone have any suggested guidelines for those numbers?
I'm using 100 or higher for seeks and scans combined, and 250 for user_cost or higher.
Obviously, if I'm going to implement one of these index suggestions, I'll need to weigh the impact of maintaining the index as well.
Last, the code I'm using (and if anyone recognizes it, let me know whose it is so I can credit them:)
SELECT
db_name(mid.database_id) as [DBName],
statement AS [database.scheme.table],
column_id,
column_name,
column_usage,
mid.included_columns,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.avg_total_user_cost,
migs.avg_user_impact,
(migs.user_seeks + migs.user_scans) * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01) as [Index Advantage]
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle=migs.group_handle
where db_name(mid.database_id) not in ('msdb','master','model','tempdb')
and migs.avg_total_user_cost >= 250
and migs.user_seeks + migs.user_scans >= 100
ORDER BY mid.database_id, migs.avg_total_user_cost
October 3, 2013 at 9:24 am
I use Glenn Berry's scripts to find missing indexes (along with a lot of other great information). In his script he is already doing something similar to what you are suggesting, so have a look at his scripts. http://bit.ly/18Tt1Ei
In the scripts you will find missing indexes sorted by performance impact and there is also a script that will show you which objects/statements will benefit from adding missing indexes. As always test, test, test the changes! Get a good baseline of performance first and then add the index in a test environment.
I do this once a month cherry pick out 5 indexes to add and then test it. Next month rinse and repeat.
October 3, 2013 at 9:29 am
Glenn's script is pretty good and I also like Brent Ozar Unlimited's sp_BlitzIndex.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2013 at 12:07 pm
Another approach is to use the queries in cache and the execution plans missing index hints to identify the queries that are called frequently or are actually running slow that also have missing index hints. I do a little bit of that in this blog post[/url].
"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