Here is my script to find missing indexes using the missing index DMVs. It’s pretty much like others with the exception that I first get the instance uptime and then use that to calculate the number of seeks and scans per day. That way I can make my index decisions both in terms of impact and frequency of use.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | DECLARE @uptime VARCHAR(20); SELECT @uptime = DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb'; SELECT @@SERVERNAME AS [server], @uptime AS [uptime_days] , DB_NAME() AS [DATABASE], SCHEMA_NAME(schema_id) AS [schema], sys.objects.name AS [TABLE] , user_seeks , user_scans , ( user_seeks + user_scans ) / @uptime AS [seeks&scans/DAY] , avg_total_user_cost , ( avg_total_user_cost * avg_user_impact ) * ( user_seeks + user_scans ) AS Impact , 'CREATE NONCLUSTERED INDEX ix_' + sys.objects.name + '_ ON ' + SCHEMA_NAME(schema_id) + '.' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) on [PRIMARY];' AS CreateIndexStatement , mid.equality_columns , mid.inequality_columns , mid.included_columns FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() INNER JOIN sys.objects WITH ( NOLOCK ) ON mid.object_id = sys.objects.object_id WHERE ( migs.group_handle IN ( SELECT TOP ( 500 ) group_handle FROM sys.dm_db_missing_index_group_stats WITH ( NOLOCK ) ORDER BY ( avg_total_user_cost * avg_user_impact ) * ( user_seeks + user_scans ) DESC ) ) AND OBJECTPROPERTY(sys.objects.object_id, 'isusertable') = 1 ORDER BY 8 DESC; |
