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:

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:

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
	/*
		Max 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:

EXEC dbo.RemoveMissingIndexSuggestions @Database = 'tempdb', @Table = 'SomeTable';