SQL Server 的 DMV 建議的缺失索引數量限制的解決方法

在生產環境中，每個 SQL Server 實例都有超過 250 個數據庫（我們稱之為“OrgDb”）。我目前正在進行的項目旨在將 SQL Server 的 DMV 報告的所有缺失索引發送到遙測，以便對發送到這些 orgDb 的查詢的執行情況進行一些後期分析，並可能進行一些優化。

聽起來很簡單，對吧？然而問題是，DMV 可以在單個 SQL Server 中報告的缺失索引數量的最大限制為 500，而我們預計每個 OrgDb 大約有 20 個缺失索引（總共約 5000 個）。

誰能想到這個限制的解決方法？我最初想到的一個解決方案是刪除 DMV 缺失的索引表：

sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group

每次更新後，但事實證明這些表無法修改：

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

您無法重置 DMV，但是您可以通過在 DMV中提到的表上創建一個小的過濾索引然後立即刪除該索引來解決此限制並從 DMV 中刪除行。

例如：

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

DROP INDEX dbo.SomeTable.IX_temp;

我創建了一個腳本來自動化這個過程。

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

這可以通過將@Database參數設置為數據庫名稱來執行，以消除僅與該數據庫相關的推薦索引，或者不使用參數來消除所有推薦索引。@Table通過將表的名稱傳遞到參數中，可以選擇將其限制為單個表的建議。

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

它最多為每個表創建一個索引。PRIMARY KEY索引具有唯一的名稱，並使用單個列構造，如果有表，則優先使用表的。如果表沒有主鍵或至少以下類型的列之一，則此過程將失去表：

bigint
binary
hierarchyid
int
uniqueidentifier
varbinary

我在SQLServerScience上寫了一篇關於這個問題的簡短部落格文章

引用自：https://dba.stackexchange.com/questions/134799