德瑞克：SQL Server 學習筆記: 淺談遺漏索引(missing index)

在SQL Server 2005 與 2008 版本上，提供了「遺漏索引(missing index)」。

遺漏索引功能是一種可隨時在資料庫資料表和索引檢視上識別遺漏索引的輕量型方法

查詢最佳化工具產生查詢計畫時，它會分析特定篩選條件的最佳索引。
如果最佳索引不存在，則查詢最佳化工具會產生次佳的查詢計畫，但仍會儲存這些索引的相關資訊。

遺漏索引功能可讓您存取這些索引的資訊，讓您決定是否應該實作它們。

預設會開啟遺漏索引功能。未提供任何控制項來開啟或關閉這個功能，或重設在查詢動態管理物件時所傳回的任何資料表。
重新啟動 SQL Server 時，會卸除所有遺漏的索引資訊。

只有在搭配使用 -x 引數與 sqlservr 命令提示字元公用程式以啟動 SQL Server 執行個體時，才可停用這個功能。

遺漏索引功能的元件會在其輸出中列出相等、不相等和內含資料行。

例如，XML 執行程序表 MissingIndexes 元素指出是否要在 Transact-SQL 陳述式述詞中使用索引鍵資料行代表相等 (=) 或不相等 (<、> 等等)，或是只包含該索引鍵資料行來涵蓋查詢。
它會將這項資訊顯示成 ColumnGroup 子元素之 Usage 屬性的下列其中一個值：
1. usage="EQUALITY"
2. usage="INEQUALITY"
3. usage="INCLUDE"

請遵照下列方針，在利用遺漏索引功能元件輸出所撰寫的 CREATE INDEX 陳述式中排序資料行：
1. 將相等資料行列在最前面 (資料行清單的最左邊)。
2. 將不相等資料行列在相等資料行後面 (列出的相等資料行右邊)。
3. 將內含資料行列在 CREATE INDEX 陳述式的 INCLUDE 子句中。
4. 若要決定相等資料行的有效順序，請依據其選擇性排列這些資料行；也就是將選擇性最高的資料行列在最前面。

遺漏索引功能具有下列限制：
1. 不能用來微調索引組態。
2. 不能收集超過 500 個遺漏索引群組的統計資料。
3. 不能指定索引中使用的資料行順序。
4. 對於只涉及不相等述詞的查詢，傳回的成本資訊比較不精確。
5. 僅報告某些查詢的內含資料行，因此必須手動選取索引鍵資料行。
6. 只會傳回有關可能遺漏索引之資料行的原始資訊。
7. 它不建議已篩選的索引。
8. XML 執行程序表中重複出現的同一個遺漏索引群組，可能會傳回不同的成本。
9. 它不會考慮一般查詢計畫。

遺漏索引功能是一種輕量型工具，用來尋找可大幅提升查詢效能的遺漏索引。
它並未提供足夠的資訊讓您微調索引組態。請使用 Database Engine Tuning Advisor 來達成此目的。

Database Engine Tuning Advisor 是獨立的工具，用以分析整個 SQL Server 工作負載，以及產生實體設計結構組態的建議 (例如索引、索引檢視或資料分割)。
以下提供 Database Engine Tuning Advisor 與遺漏索引功能的詳細比較。

遺漏索引是輕量型伺服器端一律啟動功能，用於捕捉和更正錯誤。
相反地，Database Engine Tuning Advisor 是完整工具，用以存取實體資料庫設計，以及建議新的實體設計結構以改進效能。
Database Engine Tuning Advisor 不僅會考慮索引，也會考慮是否應該使用索引檢視或資料分割來改進查詢效能。

下表詳細比較遺漏索引功能和 Database Engine Tuning Advisor：

如果實作識別的遺漏索引，可能會改進查詢效能。
您可使用 Database Engine Tuning Advisor 使用者指定的組態功能和評估模式，來決定實作遺漏索引對整個工作負載的影響。

即使對於單一查詢工作負載，Database Engine Tuning Advisor 和遺漏索引功能都可傳回不同的建議。
因為遺漏索引功能建議之索引的索引鍵資料行不區分順序，所以會發生這種情況。

另一方面，Database Engine Tuning Advisor 建議包含索引之索引鍵資料行的順序，以最佳化查詢效能。

摘要
遺漏索引功能可用來捕捉和更正索引錯誤，而 Database Engine Tuning Advisor 可用來更正索引錯誤以及完整微調伺服器上的工作負載。
您可使用遺漏索引功能來產生候選索引，但應使用 Database Engine Tuning Advisor 來驗證它們。

sys.dm_db_missing_index_columns 動態管理函數
傳回有關遺漏索引之資料庫表格資料行的資訊。
sys.dm_db_missing_index_columns 是動態管理函數。

sys.dm_db_missing_index_columns 傳回的資訊會在查詢最佳化工具最佳化查詢時更新，而不會一直保存。
遺漏索引資訊只會保留到 SQL Server 重新啟動為止。
如果資料庫管理員想要在伺服器回收之後保留遺漏索引資訊，應該定期製作該項資訊的備份副本。

關於資料行：column_usage 之值的說明
EQUALITY 作為表示相等之述詞的資料行，格式如下：
table.column = constant_value

INEQUALITY 作為表示不相等之述詞的資料行，例如，格式如下的述詞：
table.column > constant_value
"=" 以外的其他任何比較運算子都可表示不相等。

INCLUDE
資料行不是用來評估述詞，而用於其他原因，例如用來涵蓋查詢。

sys.dm_db_missing_index_columns 傳回的資訊會在查詢最佳化工具最佳化查詢時更新，而不會一直保存。
遺漏索引資訊只會保留到 SQL Server 重新啟動為止。

如果資料庫管理員想要在伺服器回收之後保留遺漏索引資訊，應該定期製作該項資訊的備份副本。

請參考以下的範例程式碼：

-- 建立資料表：tindx1
USE Northwind
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tindx1]') AND type in (N'U'))
DROP TABLE [dbo].[tindx1]
GO
CREATE TABLE tindx1
(sid INT identity , cDateTime datetime,cData char(8000))
GO
-- 新增資料列：5 萬筆  
SET NOCOUNT ON 
GO  
DECLARE @cnt INT=1  
WHILE @cnt<=50000 
BEGIN  
INSERT tindx1 VALUES( GETDATE() ,GETDATE())  
SET @cnt+=1 
END
GO
/*=========================================================*/
--01 檢視資料表：tindx1 的索引類型  
SELECT t.name N'資料表', i.name N'索引名稱', i.type_desc N'索引類型的描述'  
FROM sys.tables t INNER JOIN sys.indexes i  
ON t.object_id = i.object_id  
WHERE t.name = 'tindx1'
GO
EXEC sp_helpindex 'tindx1'
GO
--02 執行一般查詢時，沒有最佳可用的索引
SELECT cDateTime,cData
FROM tindx1
WHERE sid=1

/*=========================================================*/
--03 使用動態管理函數來分析：「遺漏索引(missing index)」
SELECT *
FROM sys.dm_db_missing_index_details

--
SELECT mig.*, statement N'資料表',
column_id, column_name N'資料行', column_usage N'查詢使用資料行的方式',included_columns N'內含資料行(Inclueded Columns)'
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

顯示，建議：在資料行：sid 上建立索引。

--04 建立索引，例如：
CREATE CLUSTERED INDEX [_dta_index_tindx1_c_8_2098106515__K1] ON [dbo].[tindx1]
(
[sid] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
--04 再度執行一般查詢時，已經有最佳可用的索引
SELECT cDateTime,cData
FROM tindx1
WHERE sid=1
--05 再度使用動態管理函數來分析：「遺漏索引(missing index)」，已經沒有「遺漏索引(missing index)」。
SELECT *
FROM sys.dm_db_missing_index_details
--
SELECT mig.*, statement N'資料表',
column_id, column_name N'資料行', column_usage N'查詢使用資料行的方式',included_columns N'內含資料行(Inclueded Columns)'
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;