December 26, 2012 at 11:53 pm
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...
-thanks
veerbharat
December 28, 2012 at 5:40 am
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.
SELECT
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
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply