Tuesday, April 13, 2010

Missing Index for SQL Server for slow performance

This query will script indexes create statements when you have slow performance with SQL server. After applying the indexes you should see some improvement. SQL Server keeps an internal list of indexes it believe that you should create to speed things up and this query will show them to you. This is not as thorough / accurate as using the Index Tuning Wizard and you do not know how large the indexes will be. The scripts with the highest improvement_measure should be tested first because you would see the most benefit. The tables that hold the recommended indexes are cleared every time you restart SQL server and are not populated until you use put a load on the database. If you do not see a query improve in speed after testing the index, it may still be improving but you did not notice. Check to see if the CPU / Memory/ Disk usage usage was reduced. I recommend adding this view to your database and selecting from it from time to time to see what shows up.

Create view [dbo].[usp_Missing_Indexes] as

select * from
'CREATE INDEX [experimental_index_' + CONVERT (varchar, missingGroups.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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,
stats.avg_total_user_cost * stats.avg_user_impact * (stats.user_seeks + stats.user_scans) as BenefitWeight,
stats.avg_total_user_cost * (stats.avg_user_impact / 100.0) * (stats.user_seeks + stats.user_scans) AS improvement_measure,
stats.user_seeks + stats.user_scans as SeeksScans,

FROM sys.dm_db_missing_index_groups missingGroups
INNER JOIN sys.dm_db_missing_index_group_stats stats ON stats.group_handle = missingGroups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON missingGroups.index_handle = mid.index_handle
) a
where improvement_measure > 100
--order by BenefitsWeight