Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to telemetry, in order to do some post analysis on how well the queries sent to these orgDbs are doing and possibly do some optimizations.

Sounds straightforward, right? The problem however, is that there is a max limit of 500 for the number of missing indexes that DMVs can report in a single SQL Server whereas we expect around 20 missing indexes per OrgDb (~ 5000 in total).

Can anyone think of a workaround for this limit? One solution I initially thought of, was to delete the DMVs' missing index tables:

sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group

after each update, but turns out those tables cannot be modified:

Error:Ad hoc updates to system catalogs are not allowed.

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index.

For instance:

CREATE INDEX IX_temp
ON dbo.SomeTable(SomeKey)
WHERE SomeKey IS NULL;

DROP INDEX dbo.SomeTable.IX_temp;

I've created a script to automate this process.

```
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;

StackExchange Database Administrators Q#134799, answer score: 5