When to add indexes suggested from the DMVs

Question

When to add indexes suggested from the DMVs

dndaughtery

SSChampion

Points: 11290
More actions
November 18, 2014 at 8:43 am

#397653

I have a the following query I use to search for missing indexes:
USE MyDB
GO
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
Where should the cutoff be as far as Avg_Estimated_Impact should I stop at considering creating the index?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I don't think that there's a number that anyone can give you. You have to be wary about adding suggested indexes. I like to at least do the following before adding any indexes:

1. Examine existing indexes and usage. Is there an existing index I can modify instead of adding a new index?

2. Find out what queries may benefit from the new or modified index. If access mostly through stored procedures this is easier as you can go through the code manually, if not, you can query the plan cache, check out this post by Grant Fritchey, http://www.scarydba.com/2009/03/02/more-refinements-on-the-missing-indexes-query/.

This can be time consuming but in the long run I think you get better performance because you have the correct indexes and not too many indexes.

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

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

The first thing to do with indexing is to review index usage stats and missing index stats together, at a minimum. If you're really serious, you also look at operational stats, but you can get by without it.

First determine if every significant table has the best clustered index on it. If not, make those changes first. Don't get caught up in making sure it's a unique key, as long as you can identify the correct column(s) in the correct order. Ignoring the clus index and just slapping extra nonclustered indexes on tables might appear to give better performance, esp. over the short term, but long term you'll actually end up making things worse, or even far worse.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101121 More actions · Answer 3

Btw, keep in mind that for multi-key-column indexes, SQL's missing index output does not consider the best order for the keys, which they freely admit. From Books Online, they do offer a guideline:

"To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first."

But that's not necessarily 100% right either, it depends on your specific table and usage.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".