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;
FETCH NEXT FROM cur INTO @ObjectName, @DatabaseName, @CreateStmt, @DropStmt;
END
CLOSE cur;
DEALLOCATE cur;
END
GO
This can be executed with the @Database
parameter set to the name of a database to eliminate recommended indexes only related to that database, or without parameters to eliminate all recommended indexes. One can optionally limit this to the suggestions for a single table by passing the name of the table into the @Table
parameter.
EXEC dbo.RemoveMissingIndexSuggestions @Database = 'tempdb', @Table = 'SomeTable';
It creates at most one index per table. The index has a unique name and is constructed using a single column, giving priority to using the table's PRIMARY KEY
, if it has one. Tables will be missed by this proc if the table does not have a primary key or at least one of the following types of columns:
bigint
binary
hierarchyid
int
uniqueidentifier
varbinary
I've written a short blog post on this issue over at SQLServerScience