Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

The maximum allowable avg_user_impact for any missing index Expand / Collapse
Posted Wednesday, December 26, 2012 11:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:29 AM
Points: 2, Visits: 179
Hello everyone,
I am required to create some indexes on the table to improve the performance of the database queries
i have found certain missing indexes but m not sure which of them should i create.
Just wanted to know if there is any threshold value for the avg_user_impact?
or what is the maximum value that can be allowed for the avg_user_impact?

Any comment/suggestion would be appreciated...

Post #1400480
Posted Friday, December 28, 2012 5:40 AM



Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 5,667, Visits: 8,192
1) BE VERY VERY VERY careful about slapping indexes on your system using the missing indexes subsystem (or Database Tuning Advisor). They LOVE LOVE LOVE included columns, and they do NO CHECKING WHATSOEVER for existing indexes!! I have spend literally HUNDREDS of manhours cleaning up client systems from rampant use of one or the other or both of those constructs. I am about to spend another hundred or so on yet another client here in early 2013. Be sure to check recommendations with what actually currently exists on the table and do something smart.

2) Here is a script I use to see relative value of missing indexes.

mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1400882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse