通過添加新索引獲得的查詢

我如何能夠找出通過添加新索引可以獲得哪些查詢。

例如：假設我在表上添加了一個新的 NCI，我如何能夠找出哪些查詢會從添加這個 NCI（或任何索引）中受益？

我贊同喬納森的評論。有許多資源（向 DBA 展示）表明自動化的“缺失索引”分析非常有限，而且常常具有誤導性。

除了 sp_blitzindex 之外，如果您讓他們對每個數據庫執行以下查詢（在有代表性的正常執行時間之後），您可以了解哪些表是由於大量掃描而可能導致不必要 IO 的原因（考慮行數) 和查找。您可以開始考慮僅為這些表建議的索引，和/或找到有問題的查詢並考慮為它們建立索引。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT  OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
   , OBJECT_NAME(p.object_id) AS [Table]
   , i.name AS [Index]
   , i.type_desc AS [Index Type]
   --, p.partition_number
   , p.rows AS [Row Count],
      USER_SEEKS, 
      USER_SCANS, 
      USER_LOOKUPS, 
      USER_UPDATES
FROM sys.partitions p 
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                        AND p.index_id = i.index_id
left join (select * from SYS.DM_DB_INDEX_USAGE_STATS  where DATABASE_ID = DB_ID(DB_NAME())) b ON b.object_id = i.object_id and b.index_id = i.index_id 
WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'

為了查看哪些程序正在使用特定的表，（and therefore potentially benefiting (or getting worst) from the addition of a missing indexes）我有幾個腳本可以幫助你：

第一個腳本顯示了該過程和該過程使用的表：

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name 

這是上面腳本的變體，編寫方式略有不同：

--another way

/*
     Get the DB Tables used in a Stored Procedure  
     It used from sysdepends, sysobjects table to gather information
     It may not be accurate if the DB is not refreshed

*/

SELECT @@SERVERNAME
     ,DB_NAME()

DECLARE @sp_name SYSNAME

SELECT @sp_name = 'udpDatabaseReindex'

SELECT o.id, o.name as 'Procedure_Name' 
        , oo.name as 'Table_Name'
        --, d.depid
FROM SYSDEPENDS d
    LEFT OUTER JOIN SYSOBJECTS o
                 ON o.id=d.id 
    LEFT OUTER JOIN SYSOBJECTS oo
                 ON oo.id=d.depid

WHERE  o.name= @sp_name   -- Stored Procedure Name
GROUP BY o.id
      , o.name 
      , oo.name

ORDER BY o.name

您可以使用 Greg Larsen 編寫的以下腳本來幫助比較添加索引之前和之後的情況。

它測量cpu和I/O。

---------------------------------------
-- Greg Larsen
-- Reduced Resource Numbers due to SUM
-- I found another bug in my SQL Server 2005 code for identifying the stored procedure statistics. Here is the fixed code:

SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(st.dbid)END AS DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) AS [SCHEMA_NAME] 
,OBJECT_NAME(st.objectid,dbid)AS [OBJECT_NAME]
,MAX(qs.creation_time) AS 'cache_time'
,MAX(qs.last_execution_time) AS 'last_execution_time'
,MAX(cp.usecounts) AS [execution_count]
,SUM(total_worker_time) / MAX(cp.usecounts) AS AVG_CPU
,SUM(total_elapsed_time) / MAX(cp.usecounts) AS AVG_ELAPSED
,SUM(total_logical_reads) / MAX(cp.usecounts) AS AVG_LOGICAL_READS
,SUM(total_logical_writes) / MAX(cp.usecounts) AS AVG_LOGICAL_WRITES
,SUM(total_physical_reads) / MAX(cp.usecounts)AS AVG_PHYSICAL_READS 
FROM sys.dm_exec_query_stats qs 
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE objtype = 'Proc' 
AND text
NOT LIKE '%CREATE FUNC%' 
GROUP BY cp.plan_handle,DBID,objectid;

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