|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


I don't remember where I got the base for this query, its been very helpful for me to run across databases and servers to get a list of missing indexes to examine. 

Key Point: Do not just add the indexes identified as missing. Look at the indexes, verify they are not duplicate indexes, TEST the indexes. Many of the "MISSING" indexes won't improve the performance of the query. Some indexes may improve the query performance a little, but will add additional index maintenance overhead for updates and deletes. You must always weight the cost vs. benefit.


SELECT
mid.statement,
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
OBJECT_NAME(mid.Object_id) as TableName,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
'CREATE INDEX [IX_' + OBJECT_NAME(mid.Object_id) + COALESCE('_' + PARSENAME(mid.equality_columns,1),'')
+ COALESCE('_' + PARSENAME(mid.inequality_columns,1),'')
+ CASE WHEN mid.included_columns IS NOT NULL THEN '_wInc' ELSE '' END + ']'
+ ' 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.*
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 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
AND mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
OPTION(RECOMPILE)