求助：优化MS SQL Server缺失索引检测脚本，过滤已有索引

我有一套用于Microsoft Dynamics NAV（基于MS SQL Server）的缺失索引检测SQL脚本，当前存在以下问题：

• 脚本会识别出部分**已存在索引仅缺少大量包含列（通常占表中列的70%以上）**的情况，我们不希望处理这类场景
• 同时需要确保不生成包含列数量超过10个的索引

我尝试通过数据库名、表名和等值列过滤已有索引，但以下方案均未成功：

• ChatGPT生成的脚本无法运行
• 自行合并现有索引查询脚本时出现错误，无法正常工作
• 自行修改脚本也报错
• 未找到有效解决方案

现有缺失索引检测脚本 #

USE [master]
GO

SET NOCOUNT ON
GO

-- >> Missing Indexes <<
 
-- Filter-Values
declare @threshold_occurrence int, @threshold_impact int, @threshold_age int, @threshold_include int

set @threshold_occurrence = 10 -- Avg. Proposals per Day online; change on demand
set @threshold_impact = 90 -- Percent; change on demand
set @threshold_age = 14 -- last proposal date; change on demand
set @threshold_include = 10 -- maximum number of included columns used in CREATE; change on demand

--

declare @daysonline int
select @daysonline = datediff(dd, create_date, getdate()) + 1 from sys.databases where name = 'tempdb'
declare @dbname sysname, @tsql nvarchar(max)
declare db_cur cursor fast_forward for
  select [name] from sys.databases 
  where [state] = 0 
    --and [name] not in ('master', 'model', 'msdb', 'tempdb', 'SSI')  -- exclude databases
    and [name] in ('XXXX') -- include databases 
  order by [name]
open db_cur
fetch next from db_cur into @dbname
while @@FETCH_STATUS = 0
begin
    set @tsql =
    '
    USE [' + @dbname + ']
    SELECT mig.index_handle, migs.user_seeks, migs.last_user_seek, migs.user_scans, migs.last_user_scan, migs.avg_user_impact,
                                CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage],
                                db_name(mid.database_id) as db, object_name(mid.object_id) as object, mid.equality_columns, mid.inequality_columns, mid.included_columns,
                                ''CREATE INDEX [ssi_'' + convert(varchar, mig.index_handle) + ''_'' + replace(replace(replace(convert(varchar(30), getdate(), 120), ''-'',''''), '':'',''''), '''' , ''_'') + ''] ON ['' + OBJECT_SCHEMA_NAME(mid.[object_id]) + ''].['' +  object_name(mid.object_id) + ''] '' + ''('' +
                                  CASE WHEN mid.equality_columns is not null THEN mid.equality_columns ELSE '''' END +
                                  CASE WHEN mid.equality_columns is null AND mid.inequality_columns is not null THEN mid.inequality_columns ELSE '''' END +
                                  CASE WHEN mid.equality_columns is not null AND mid.inequality_columns is not null THEN '', '' + mid.inequality_columns ELSE '''' END + '')'' +
                                  CASE WHEN (mid.included_columns is not null) AND ( (SELECT LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, '','',''''))) < ' + convert(varchar(5), @threshold_include) + ' ) 
                                  THEN '' INCLUDE ('' + mid.included_columns + '') '' 
                                  ELSE '''' 
                                  END + '' WITH (MAXDOP = 64, ONLINE = OFF, DROP_EXISTING = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);''  COLLATE DATABASE_DEFAULT as tsql
    FROM sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig
                   ON (migs.group_handle = mig.index_group_handle)
    INNER JOIN sys.dm_db_missing_index_details AS mid
                   ON (mig.index_handle = mid.index_handle)
    WHERE (mid.database_id = db_id())
    AND ((migs.user_seeks / ' + convert(varchar(5), @daysonline) + ') >= ' + convert(varchar(5), @threshold_occurrence) + ')  
    AND (migs.avg_user_impact >= ' + convert(varchar(5), @threshold_impact) + ') 
    AND (datediff(dd, migs.last_user_seek, getdate()) <= ' + convert(varchar(5), @threshold_age) + ') -- propsed within period 
    ORDER BY object_name(mid.object_id), replace(replace((mid.equality_columns + mid.inequality_columns), '', '', ''''), '','', ''''), mid.included_columns
    COLLATE DATABASE_DEFAULT;
    '

    --print @tsql
    exec sp_executesql @tsql

    fetch next from db_cur into @dbname
end
close db_cur
deallocate db_cur
GO

已尝试合并的现有索引查询脚本 #

DECLARE @TableName NVARCHAR(50) = 'Cronus$Sales Price'

;WITH CTE_INDEX_DATA AS (
       SELECT
              SCHEMA_DATA.name AS schema_name,
              TABLE_DATA.name AS table_name,
              INDEX_DATA.name AS index_name,
              STUFF((SELECT  ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)
                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
                                                ON T.object_id = INDEX_DATA_KEY_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
                                                ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
                                                ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
                                  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS key_column_list ,
          STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name
                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_INC_COLS
                                                ON T.object_id = INDEX_DATA_INC_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
                                                ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_INC_COLS
                                                ON T.object_id = COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
                                  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS include_column_list,
       INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
       FROM sys.indexes INDEX_DATA
       INNER JOIN sys.tables TABLE_DATA
       ON TABLE_DATA.object_id = INDEX_DATA.object_id
       INNER JOIN sys.schemas SCHEMA_DATA
       ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
       WHERE TABLE_DATA.is_ms_shipped = 0
       AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')
       --AND INDEX_DATA.name NOT LIKE '%$%'
       AND [TABLE_DATA].[object_id] = (select object_id from sys.objects where name = @TableName)
)
SELECT
       [DUPE1].*
       ,(SELECT COUNT(*) FROM CTE_INDEX_DATA DUPE2
         WHERE DUPE1.schema_name = DUPE2.schema_name
         AND DUPE1.table_name = DUPE2.table_name
         AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
         AND DUPE1.index_name <> DUPE2.index_name) AS [CountOfSimilarIndexes]
FROM CTE_INDEX_DATA DUPE1
--WHERE EXISTS      /*Einschränkung auf Indexe, die in ähnlicher Form noch mal vorliegen*/
--(SELECT * FROM CTE_INDEX_DATA DUPE2
-- WHERE DUPE1.schema_name = DUPE2.schema_name
-- AND DUPE1.table_name = DUPE2.table_name
-- AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
-- AND DUPE1.index_name <> DUPE2.index_name)
ORDER BY DUPE1.table_name, DUPE1.key_column_list

内容的提问来源于stack exchange，提问作者disnamedyna