您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005+ 動態管理檢視 (DMV) 評估與管理索引完整指南

編輯：關於SqlServer

SQL Server 2005 引入動態管理檢視 (DMV)，提供工作階段、交易、請求等伺服器狀態資訊，可用於診斷、記憶體調校、效能監控（SQL 2000 不支援）。善用 sys.dm_db_index_usage_stats、sys.dm_db_missing_index_* 等 DMV，能量化索引「維護成本」與「查詢效益」，科學決定保留、新增或刪除索引。

核心觀念：索引成本與收益

維護成本：INSERT 需在所有索引寫入；UPDATE/DELETE 先讀取定位再寫入；FK 約束額外讀取參考表。
查詢收益：SEEK/SCAN/LOOKUP 計數反映索引被查詢使用頻率。
決策原則：user_updates 遠大於 user_seeks+scans+lookups 時，索引可能「只寫不讀」，評估刪除。

1. 找出從未使用的索引（含堆表）

DECLARE @dbid INT = DB_ID('Northwind');
SELECT
  OBJECT_NAME(i.object_id) AS TableName,
  i.name AS IndexName,
  i.index_id
FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
  AND i.index_id NOT IN (
    SELECT s.index_id
    FROM sys.dm_db_index_usage_stats s
    WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id
      AND s.database_id = @dbid
  )
ORDER BY TableName, i.index_id, IndexName;

index_id=0 為堆表，=1 為叢集索引。結果為完全無使用統計的索引，優先評估移除。

2. 找出缺失索引建議（前 50 高影響力）

SELECT TOP 50
  ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0) AS [Total Cost],
  avg_user_impact,
  statement AS TableName,
  equality_columns AS [EqualityUsage],
  inequality_columns AS [InequalityUsage],
  included_columns AS [IncludeColumns],
  user_seeks, user_scans
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE statement LIKE '%YourTable%'
ORDER BY [Total Cost] DESC;

Total Cost 綜合成本節省估算，優先實作高分建議。實作前必須驗證：欄位基數、寫入頻率、現有索引覆蓋度。

3. 找出低使用率索引（含欄位清單與刪除腳本）

WITH IXC AS (
  SELECT ic.object_id, ic.index_id, ic.index_column_id, ic.is_descending_key, ic.is_included_column, c.name AS column_name
  FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id
)
SELECT TOP 50
  o.name AS TableName,
  i.name AS IndexName,
  i.index_id,
  dm.user_seeks, dm.user_scans, dm.user_lookups, dm.user_updates,
  p.TableRows,
  STUFF(IXC_COL.index_columns,1,2,N'') AS IndexColumns,
  STUFF(IXC_COL_INC.index_columns_include,1,2,N'') AS IncludedColumns,
  'DROP INDEX ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + '.' + QUOTENAME(i.name) AS DropStatement
FROM sys.dm_db_index_usage_stats dm
JOIN sys.indexes i ON i.index_id = dm.index_id AND dm.object_id = i.object_id
JOIN sys.objects o ON dm.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN (SELECT SUM(rows) AS TableRows, index_id, object_id FROM sys.partitions GROUP BY index_id, object_id) p
  ON p.index_id = dm.index_id AND dm.object_id = p.object_id
CROSS APPLY (
  SELECT index_columns = (
    SELECT N', ' + QUOTENAME(column_name) + CASE is_descending_key WHEN 1 THEN N' DESC' ELSE N'' END
    FROM IXC WHERE object_id=i.object_id AND index_id=i.index_id AND is_included_column=0
    ORDER BY index_column_id FOR XML PATH(''), ROOT('r'), TYPE
  ).value('/r[1]','nvarchar(max)')
) IXC_COL
OUTER APPLY (
  SELECT index_columns_include = (
    SELECT N', ' + QUOTENAME(column_name)
    FROM IXC WHERE object_id=i.object_id AND index_id=i.index_id AND is_included_column=1
    ORDER BY index_column_id FOR XML PATH(''), ROOT('r'), TYPE
  ).value('/r[1]','nvarchar(max)')
) IXC_COL_INC
WHERE OBJECTPROPERTY(dm.object_id,'IsUserTable')=1
  AND dm.database_id = DB_ID()
  AND o.name = 'YourTable'  -- 指定表名
ORDER BY (dm.user_seeks + dm.user_scans + dm.user_lookups) ASC;

結果解讀與行動建議

現象	判讀	建議行動
user_updates 極高，seeks/scans/lookups ≈ 0	純維護成本，無查詢收益	強力候選刪除（先確認無 FK/唯一約束依賴）
seeks 高，scans 低	索引有效支援點查詢	保留
scans 高，seeks 低	可能缺乏前導欄位或統計過期	更新統計、評估調整索引欄位順序
lookups 高	非叢集索引未覆蓋查詢欄位	考慮 INCLUDE 欄位或改為叢集索引