使用遺漏索引建議調整非叢集索引

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Microsoft Fabric 中的 SQL 資料庫

遺漏索引功能是一種輕量型工具，用來尋找可大幅提升查詢效能的遺漏索引。本文介紹如何使用遺漏索引建議來有效調整索引及改善查詢效能。

遺漏索引功能的限制

查詢最佳化工具在產生查詢計劃時，會分析適合特定篩選條件的最佳索引。如果最佳索引不存在，查詢最佳化工具仍會使用可用的最低成本存取方法來產生查詢計劃，但也會儲存這些索引的相關資訊。遺漏索引功能可讓您存取這些最佳索引的資訊，讓您決定是否應該加以實作。

查詢最佳化是相當注重時間的流程，因此對遺漏索引功能有所限制。限制包括：

遺漏索引建議的根據，是在執行查詢之前，進行單一查詢最佳化的期間所做的估計。查詢執行之後，不會測試或更新遺漏索引建議。
遺漏索引功能只會建議非叢集、以磁碟為基礎的資料列存放區索引。不建議使用唯一索引和篩選索引。
建議使用關鍵欄位，但建議沒有指定這些欄位的具體順序。如需進一步了解資料行排序，請參閱本文的套用遺漏索引建議一節。
建議內含資料行，但當建議大量內含資料行時，SQL Server 不會針對結果索引的大小，執行任何成本效益分析。
遺漏索引要求可能會在不同查詢中，針對相同的資料表和資料行提供類似的索引變化。請務必檢閱索引建議，並盡可能合併。
不會對瑣碎的查詢計劃提出建議。
針對只涉及不相等述詞的查詢，成本資訊比較不精確。
最多只能針對 600 個遺漏索引群組收集建議。達到此臨界值之後，便不會再收集任何遺漏索引群組資料。

由於這些限制，在執行索引分析、設計、調整和測試時，最好只將遺漏索引建議當作資訊來源之一。遺漏索引建議並不是要您完全依照建議來建立索引。

Note

Azure SQL Database 提供自動索引調整。自動索引調整會使用機器學習，透過 AI 從 Azure SQL Database 中的所有資料庫進行水平學習，並動態改善其調整動作。自動索引調整包含驗證程序，以確保建立的索引對工作負載效能有正面改善。

檢視遺漏索引建議

遺漏索引功能是由兩大要素組成：

執行計畫的 XML 中的 MissingIndexes 元素。對於查詢最佳化工具視為遺漏的索引，此元素可讓您將這些索引與遺漏它們的查詢建立關聯。
一組可接受查詢以傳回遺漏索引資訊的動態管理檢視 (DMV)。這可讓您檢視資料庫中所有遺漏的索引建議。

檢視執行計畫中的遺漏索引建議

執行計劃概觀可以透過多種方式產生或取得：

撰寫或調整查詢時，你可以使用 SQL Server Management Studio（SSMS）顯示預估的執行計畫而不執行查詢，或執行查詢後顯示實際執行計畫。
使用查詢存放區監視效能；啟用後，查詢存放區會收集執行方案。
您可以查詢 DMV (例如 sys.dm_exec_text_query_plan)，以識別已快取的執行計畫。

例如，您可以使用下列查詢，針對 AdventureWorks 範例資料庫產生遺漏的索引要求。

SELECT City, StateProvinceID, PostalCode
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;
GO

如何產生並檢視遺漏索引要求：

開啟 SSMS，並連線至您的 AdventureWorks 範例資料庫複本。
將查詢貼到工作階段中，然後在 SSMS 中針對該查詢按一下工具列上的 [顯示預估執行計畫] 按鈕，以產生預估執行計畫。執行計畫會在目前工作階段的窗格中顯示。綠色的遺漏索引陳述式會出現在圖形計畫的頂端附近。

單一執行計畫可能會包含多個遺漏索引要求，但圖形執行計畫中只能顯示一個遺漏索引要求。若要檢視執行計畫的遺漏索引完整清單，其中一個選項是檢視執行計畫 XML。
以滑鼠右鍵按一下執行計畫，然後從功能表中選取 [顯示執行計畫 XML...]。

執行計畫 XML 會在 SSMS 中以新的索引標籤開啟。

Note

只有單一遺漏索引建議會顯示在 [遺漏索引詳細資料...] 功能表選項中，即使執行計畫 XML 中有多個建議也一樣。顯示的遺漏索引建議，對於查詢來說可能不是預估改善效果最好的建議。
使用 CTRL+f 快速鍵顯示 [尋找] 對話方塊。
搜尋 MissingIndex。

在此範例中，有兩個 MissingIndex 元素。
- 第一個遺漏的索引表示，該查詢可能會在 Person.Address 資料表上使用一個索引，以支援對 StateProvinceID 資料行的相等搜尋，而該索引還包含另外兩個資料行：City 和 PostalCode。在最佳化時，查詢最佳化工具認為此索引可能會降低 34.2737% 的查詢估計成本。
- 第二個遺漏的索引表示，該查詢可能會在 Person.Person 資料表上使用可支援對 FirstName 資料行進行不等式搜尋的索引。在最佳化時，查詢最佳化工具認為此索引可能會降低 18.1102% 的查詢估計成本。

資料庫中每個以磁碟為基礎的非叢集索引都會佔用空間，還會提高插入、更新和刪除作業的額外負荷，而且可能需要維護。基於這些原因，最佳做法是先檢閱資料表和資料表上現有索引的所有遺漏索引要求，再根據查詢執行計畫新增索引。

在 DMV 中檢視遺漏索引建議

您可查詢下表中所列的動態管理物件，以擷取遺漏索引相關資訊。

動態管理檢視	傳回的資訊
sys.dm_db_missing_index_group_stats	傳回遺漏索引群組的摘要資訊，例如可藉由實作特定的遺漏索引群組而獲得的效能提升。
sys.dm_db_missing_index_groups	傳回特定遺漏索引群組的資訊 (例如，群組識別碼和該群組所包含之所有遺漏索引的識別碼)。
sys.dm_db_missing_index_details	傳回遺漏索引的詳細資訊；例如，它會傳回遺漏索引之資料表的名稱和識別碼，以及應組成遺漏索引的資料行和資料行類型。
sys.dm_db_missing_index_columns	傳回遺漏索引之資料庫資料表資料行的資訊。

下列查詢會使用遺漏的索引 DMV 來產生 CREATE INDEX 陳述式。此處的建立索引陳述式旨在協助您於檢視對該資料表的所有要求以及該資料表上現有的索引後，撰寫您自己的 DDL。

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

此查詢在排序建議時，會依據名為 estimated_improvement 的資料行。預估的改進效果是以下列項目的組合為準：

遺漏索引要求的相關聯查詢估計查詢成本。
加入索引所造成的估計影響。這是非叢集索引可降低查詢成本的估計值。
與遺失索引請求相關的查詢中，查詢運算子 (seeks 和 scans) 的執行次數總和。如同在使用查詢存放區保存遺漏索引時所述，此資訊會定期清除。

Note

Microsoft Tiger 工具箱中的 Index-Creation 指令碼會檢查遺漏索引 DMV，並自動移除任何多餘的建議索引、剖析出低影響索引，並產生索引建立指令碼以供檢閱。如上述查詢所示，它會 NOT 執行索引建立命令。 Index-Creation 指令碼適用於 SQL Server 和 Azure SQL 受控執行個體。若為 Azure SQL 資料庫，請考慮實作自動索引調整。

請檢閱遺漏索引功能的限制，以及如何在建立索引之前套用遺漏索引建議，並修改索引名稱以符合資料庫的命名慣例。

使用查詢存放區保存遺漏索引

DMV 中的遺漏索引建議會在執行個體重新啟動、容錯移轉，以及將資料庫設為離線等事件發生時遭到清除。此外，資料表的中繼資料變更時，會從這些動態管理物件中刪除該資料表的所有遺漏索引資訊。例如，在資料表中加入或卸除資料行時，或在資料表的資料行建立索引時，都會變更資料表中繼資料。對資料表上的索引執行 ALTER INDEX 操作也會清除該資料表缺少的索引請求。

儲存在計畫快取中的執行計畫，同樣會因執行個體重新啟動、容錯移轉和將資料庫設為離線等事件而清除。執行計畫可能會因記憶體壓力和重新編譯而從快取中清除。

使用查詢存放區啟用監視效能，可以在這些事件中保存執行計劃中遺漏的索引建議。

下列查詢會根據對查詢總邏輯讀取次數的粗略估計，從查詢存放區中擷取前 20 個包含遺漏索引要求的查詢方案。資料僅限於過去 48 小時內的查詢執行。

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

套用缺少的索引建議

若要有效地使用遺漏索引建議，請遵循非叢集索引設計指導方針。使用缺少索引建議來調整非叢集索引時，請檢閱基礎資料表結構、審慎合併索引、考量索引鍵資料行的順序，以及檢閱包含的資料行建議。

檢閱基底資料表結構

根據遺漏索引建議在資料表上建立非叢集索引之前，請先檢閱資料表的叢集索引。

檢查叢集索引的方法之一，是使用 sp_helpindex 系統預存程序。例如，我們可藉由執行下列陳述式，來檢視 Person.Address 資料表上的索引摘要：

exec sp_helpindex 'Person.Address';
GO

檢查 index_description 欄。資料表只能有一個叢集索引。如果已實作資料表的叢集索引，則 index_description 將包含 'clustered' 一詞。

如果沒有叢集索引，資料表將是堆積。在此情況下，請檢查資料表是否刻意建立為堆積，以解決特定效能問題。多數資料表都能從叢集索引中獲益：資料表經常是不小心被建立成堆積表。請考慮根據叢集索引設計指導方針來實作叢集索引。

檢閱遺漏索引和現有索引以尋找重疊

遺失的索引可能會在不同查詢中，針對相同的資料表和資料行，提供類似的非叢集索引變化。遺漏索引也可能類似於資料表上的現有索引。為了獲得最佳效能，最好檢查遺失的索引和現有索引是否有重疊，並避免建立重複的索引。

將資料表中的現有索引編寫成指令碼

檢查資料表上現有索引定義的方法之一，就是使用「物件總管詳細資料」編寫索引指令碼：

將物件總管連線到您的執行個體或資料庫。
在物件總管中展開指定資料庫的節點。
展開 [資料表] 資料夾。
展開您要為其產生索引指令碼的資料表。
選取 Indexes 資料夾。
如果尚未開啟 [物件總管詳細資料] 窗格，請在 [ 檢視 ] 功能表上，選取 [物件總管詳細資料] 或按 F7。
使用快速鍵 CTRL+a，選取 [物件總管詳細資料] 窗格上所列的所有索引。
以滑鼠右鍵按一下所選區域中的任何位置，然後選取功能表選項 Script index as，然後 CREATE 選取 To** 和 New 查詢編輯器 Window。

檢閱索引，並盡可能合併

以整體方式檢視資料表的遺漏索引建議，以及該資料表上現有索引的定義。請記住，在定義索引時，一般而言應將等值資料行放在非等值資料行之前，並共同構成索引鍵。若要決定相等資料行的有效次序，請依據其選擇性排列這些資料行：將選擇性最高的資料行列在最前面 (資料行清單的最左邊)。不重複資料行最具選擇性，而具有許多重複值的資料行選擇性較低。

應使用 INCLUDE 子句，將 included columns 新增至 CREATE INDEX 陳述式。內含資料行的順序不會影響查詢效能。因此，合併索引時，包含資料行可以一併合併，而不必擔心其順序。深入了解包含的資料行指南。

例如，您可能有一個資料表 Person.Address，其索引鍵資料行 StateProvinceID 上有現有的索引。您可能會看到針對 Person.Address 資料表中下列資料行的遺漏索引建議：

StateProvinceID 和 City 的相等篩選器
適用於 StateProvinceID 和 City 的相等篩選器，INCLUDEPostalCode

若將現有索引修改為符合第二項建議，即建立一個在 StateProvinceID 和 City 上具有索引鍵，並包含 PostalCode 的索引，則很可能可滿足產生這兩項索引建議的查詢。

索引調整經常需要權衡取捨。許多資料集的情況很可能是，City 資料行比 StateProvinceID 資料行更具選擇性。但是，如果我們現有建立在 StateProvinceID 上的索引被大量使用，而其他請求大多會同時查詢 StateProvinceID 和 City，那麼對資料庫整體而言，建立一個將這兩個資料行都納入索引鍵、並以 StateProvinceID 作為前導欄位的單一索引，通常負擔會較低，儘管它不是選擇性最高的資料行。

索引可透過多種方式修改：

你可以使用 CREATE INDEX 帶有 DROP_EXISTING 條款的聲明。可依照修改情況來重新命名索引，如此便能根據命名慣例，讓名稱仍能精確地描述索引定義。
你可以使用 DROP INDEX（Transact-SQL）陳述式，後接 CREATE INDEX 陳述式。

合併索引建議時，索引鍵的順序很重要：City 作為前置資料行與 StateProvinceID 作為前置資料行不同。深入瞭解非叢集索引設計指導方針。

建立索引時，若可用，請考慮使用線上索引作業。

雖然索引在某些情況下可以大幅改善查詢效能，但索引也具有額外負荷和管理成本。請參閱一般索引設計指導方針，以利在建立索引之前評估索引的效益。

確認索引變更是否成功

請務必確認您的索引變更是否成功：查詢最佳化工具是否在使用您的索引？

驗證索引變更的其中一種方式，是使用查詢存放區來識別使用遺漏索引要求的查詢。請記下這些查詢的 query_id。可使用查詢存放區中的 [追蹤查詢] 檢視，檢查某個查詢的執行計畫是否經過變更，以及最佳化工具是否在使用新的或修改過的索引。若要深入瞭解追蹤查詢，請參閱查詢效能疑難排解入門中，。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-06-24