Missing Index DMVs have a limited capacity. How can I work around that?
The system dynamic-management-views for tracking index suggestions are limited to displaying 500 indexes at most. In a busy system with either a lot of databases or a lot of tables, this can be substantially limiting.
The system DMVs are:
|
1 2 3 4 |
sys.dm_db_missing_index_details sys.dm_db_missing_index_columns sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats |
One potential workaround for this issue is to temporarily create an index for the tables listed in sys.dm_db_missing_index_details DMV, then immediately drop those indexes. Optimally, the index would have a filter that limits the number of rows “covered” by the index to 0, since this will make running CREATE INDEX as fast as possible. I was inspired to write this post by the excellent work done by Joe Sack, and the comments made by Glenn Berry on this post on SQLSkills.com.
I’ve just created a stored procedure to handle this situation:
|
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
IF OBJECT_ID('dbo.RemoveMissingIndexSuggestions') IS NOT NULL DROP PROCEDURE RemoveMissingIndexSuggestions; GO CREATE PROCEDURE dbo.RemoveMissingIndexSuggestions ( @Database SYSNAME = NULL --optional, if NULL, clear all suggestions --if specified, only clear suggestions for that database , @Table SYSNAME = NULL --if not NULL, only clear suggestions for the specified table ) AS BEGIN /* Hannah Vernon, 2016-04-08 Inspired by work by Joe Sack and Glenn Berry at http://www.sqlskills.com/blogs/joe/clearing-missing-index-suggestions-for-a-single-table/ Creates one index for each table that is mentioned in sys.dm_db_missing_index_details then promply drops that index. The index is created with a WHERE clause that is likely to eliminate all or almost all rows, and therefore will be created quite quickly. */ SET NOCOUNT ON; DECLARE @ObjectName SYSNAME; DECLARE @DatabaseName SYSNAME; DECLARE @CreateStmt NVARCHAR(MAX); DECLARE @DropStmt NVARCHAR(MAX); DECLARE @stmt NVARCHAR(MAX); DECLARE @msg NVARCHAR(2000); DECLARE @vars NVARCHAR(1000); DECLARE @Uniquifier NVARCHAR(48); SET @vars = '@stmt NVARCHAR(MAX)'; DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC FOR WITH cte AS ( SELECT ObjectName = d.name + '.' + s.name + '.' + o.name , DatabaseName = d.name , CreateStmt = N'CREATE INDEX [IX_temp] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N'(' + mid.equality_columns + N') WHERE ' + ( SELECT TOP(1) cols.ColName FROM ( SELECT TOP(1) ColName = QUOTENAME(c.name) + N' IS NULL' FROM sys.columns c INNER JOIN sys.key_constraints kc ON c.object_id = kc.parent_object_id WHERE c.object_id = o.object_id AND kc.type_desc = N'PRIMARY_KEY_CONSTRAINT' UNION ALL SELECT TOP(1) QUOTENAME(c.name) + N' = -2147483648' FROM sys.columns c INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE ty.name IN ( N'bigint' , N'binary' , N'hierarchyid' , N'int' , N'uniqueidentifier' , N'varbinary' ) ) cols ) + ';' , DropStmt = N'DROP INDEX ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + '.[IX_temp];' , rn = ROW_NUMBER() OVER (PARTITION BY mid.object_id ORDER BY mid.index_handle) FROM sys.dm_db_missing_index_details mid INNER JOIN sys.objects o ON mid.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.databases d ON mid.database_id = d.database_id WHERE o.name NOT LIKE '#%' -- ignore temp tables AND (d.name = @Database OR @Database IS NULL) AND (o.name = @Table OR @Table IS NULL) ) SELECT cte.ObjectName , cte.DatabaseName , cte.CreateStmt , cte.DropStmt FROM cte WHERE rn = 1; OPEN cur; FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt; WHILE @@FETCH_STATUS = 0 BEGIN SET @msg = 'Flushing ' + @ObjectName + ' indexes. '; RAISERROR (@msg, 0, 1) WITH NOWAIT; SET @stmt = 'EXEC ' + QUOTENAME(@DatabaseName) + '.sys.sp_executesql @stmt;' SET @Uniquifier = CONVERT(NVARCHAR(48), NEWID(), 0); SET @CreateStmt = REPLACE(@CreateStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']') SET @DropStmt = REPLACE(@DropStmt, '[IX_Temp]', '[IX_Temp_' + @Uniquifier + ']') SET @CreateStmt = 'SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; SET QUOTED_IDENTIFIER ON; ' + @CreateStmt + ' '; SET @DropStmt = 'SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; SET QUOTED_IDENTIFIER ON; ' + @DropStmt + ' '; RAISERROR (@CreateStmt, 0, 1) WITH NOWAIT; RAISERROR (@DropStmt, 0, 1) WITH NOWAIT; EXEC sp_executesql @stmt, @vars, @stmt = @CreateStmt; EXEC sp_executesql @stmt, @vars, @stmt = @DropStmt; FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt; END CLOSE cur; DEALLOCATE cur; END GO |
For example:
|
1 2 |
EXEC dbo.RemoveMissingIndexSuggestions @Database = 'tempdb', @Table = 'SomeTable'; |