非叢集索引能以各種方式協助提升查詢效能。然而，與叢集索引不同，資料本身並不儲存在非叢集索引中。

因此，查詢時經常會發生 Lookup：回到叢集索引或 Heap，擷取未儲存在非叢集索引中的資料。在某些情況下，這是無害的行為；但在其他情況下，則可能成為效能問題。掌握處理這類常見問題的方法，有助於改善系統效能。

Lookup 用途

如同前面章節所述，資料可以儲存在 Heap 或叢集索引中。接著，可以建立額外的非叢集索引，以協助查詢搜尋資料，而不受資料實際儲存方式的限制。

查詢最佳化工具會根據這些索引對目前查詢的效用來辨識並使用它們，通常與 WHERE、JOIN 和 HAVING 子句有關。若查詢參照了非叢集索引中未包含的欄位，就必須從資料表中擷取這些資料。這個從資料來源中尋找並取得資料的過程，就是 Lookup。

SELECT p.NAME,
       AVG(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
     JOIN Production.Product AS p
       ON sod.ProductID = p.ProductID
WHERE sod.ProductID = 776
GROUP BY sod.CarrierTrackingNumber,
         p.NAME
HAVING MAX(sod.OrderQty) > 1
ORDER BY MIN(sod.LineTotal);

SalesOrderDetail 資料表在 ProductID 欄位上建立了非叢集索引。查詢最佳化工具可以使用此索引來加快資料擷取速度。

該資料表在 SalesOrderID 與 SalesOrderDetailID 欄位上定義了叢集索引，因此這兩個欄位會以資料列定位器的形式，成為非叢集索引的一部分。然而，由於查詢並未參照這些欄位，因此它們除了作為資料列定位器之外，並沒有其他作用。

查詢中所參照的其他欄位——LineTotal、CarrierTrackingNumber 與 OrderQty——既不是非叢集索引鍵的一部分，也沒有被定義為 INCLUDE 欄位。

這表示查詢最佳化工具必須執行 Lookup，才能擷取這些資料

Index Seek 作業先將回傳的資料列篩選至 228 筆。接著，執行計畫會加入一個額外的聯結作業 Nested Loops，用來配合 Key Lookup 作業，從叢集索引中取得查詢所需的其他欄位。

Lookup 所造成的效能問題

Lookup 不僅需要讀取非叢集索引所在的資料頁，也必須讀取實際儲存資料的資料頁。存取更多資料頁，會直接增加查詢的邏輯讀取次數。

此外，如果所需的資料頁不在記憶體中，Lookup 很可能需要進行隨機且連續的磁碟 I/O，從索引頁移動至資料頁。除此之外，系統還需要耗用 CPU 資源來整理資料並執行相關作業。

除了上述成本外，還必須加上將兩組資料合併所需的聯結作業成本。

正因為 Lookup 會產生這些成本，所以通常建議：透過非叢集索引擷取資料時，應盡量將結果控制在較小的資料集合內。隨著資料集合增大，Lookup 所帶來的成本也會隨之增加。

以下透過一個範例說明這一點。它會回傳相對較大的資料集合，並透過 SELECT * 取得所有欄位。這是為了示範而刻意採用的寫法。

SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 793;

雖然這是一個非常簡單的查詢，但它會回傳超過 700 筆資料列，以及資料表中的所有欄位。因此，查詢最佳化工具選擇執行 Clustered Index Scan

我們也可以使用索引提示，強制查詢最佳化工具使用 ProductID 欄位上的非叢集索引

SELECT *
FROM Sales.SalesOrderDetail AS sod WITH
    (INDEX(IX_SalesOrderDetail_ProductID))
WHERE sod.ProductID = 793;

這個查詢會將執行計畫變更為 Index Seek 搭配 Key Lookup

Lookup 成因分析

並非所有 Lookup 都必須消除，因為有些查詢的執行頻率並不高，而且目前的執行速度已經足夠快。

然而，當查詢需要進一步提升速度，且執行計畫中存在 Lookup 作業時，根據必須處理的欄位數量，這類 Lookup 可能是改善效能時相對容易處理的問題。

範例查詢使用 NationalIDNumber 欄位上的索引，從 HumanResources.Employee 資料表擷取資料。

SELECT NationalIDNumber,
       JobTitle,
       HireDate
FROM HumanResources.Employee AS E
WHERE E.NationalIDNumber = '693168613';

由於查詢只從資料表擷取三個欄位，而且我們知道索引建立在 NationalIDNumber 欄位上，因此很容易推斷 Lookup 是為了取得另外兩個欄位。

然而，對於較大型的查詢而言，欄位可能同時出現在多個子句中。此時，僅透過查看查詢內容，可能不容易判斷實際需要處理哪些欄位。

若要精確確認哪些欄位參與 Lookup 作業，可以選取 Lookup 運算子並開啟其屬性。輸出清單會提供所需資訊。

可以在此查看所有可用的詳細資訊，包括欄位、資料表及結構描述等所需內容。

在 SSMS 中，輸出清單屬性的右側會顯示省略符號按鈕。按一下該按鈕後，欄位清單會在文字視窗中開啟，方便直接複製

取得這些資訊後，即可決定要採用何種方式解決 Lookup 作業。

解決 Lookup 的技術方法

本章前面已經提過，但仍值得再次強調：並非每一個 Lookup 作業都需要立即處理。了解該查詢在整個系統中的運作情況，有助於判斷是否需要修正 Lookup。

整理一下不需要去處理的 LOOKUP 狀況

• 查詢本身已經執行得夠快。
• 查詢執行頻率很低。
• Lookup 只處理少量資料列。
• Logical Reads、CPU、執行時間都可以接受。
• 沒有造成 Blocking、I/O 壓力或整體系統效能問題。
• 修正 Lookup 所需增加的索引成本，大於它帶來的效益。

然而，通常只有當查詢執行速度不夠快時，你才會查看其執行計畫；對於執行速度已經足夠快的查詢，往往不會特別注意其中是否存在 Lookup。

要解決 Lookup，基本上有三種方法：

• 建立叢集索引
• 使用涵蓋索引
• 利用索引聯結

接下來將進一步說明這三種方法。

建立叢集索引

由於叢集索引的葉節點頁面包含資料表中的所有欄位（少數例外情況除外），因此當查詢透過叢集索引擷取資料列時，不需要再執行 Lookup。

如果先前範例所使用的索引，被重新建立為該資料表的叢集索引，就不會再出現 Lookup 作業。

然而，在大多數情況下，這並不是可行的選項。通常資料表早已依據既有的良好叢集索引完成設計，因此不能——實際上也不應該——在未經充分測試的情況下，直接交換這些索引的角色。

但如果目前使用的是 Heap 資料表，就有機會建立新的叢集索引，藉此消除所有 RID Lookup 作業。

使用涵蓋索引

我曾說明，涵蓋索引是指包含特定查詢所需全部欄位的索引。這表示查詢可以直接從索引取得所有必要欄位，不必再前往資料實際儲存的位置擷取資料。

要將非叢集索引改造成涵蓋索引，主要有兩種方式。第一種方式是修改索引鍵，將特定查詢所需的所有欄位都納入索引鍵中。這種做法會對索引產生重大影響。

--查看一下現有索引的密度向量
DBCC SHOW_STATISTICS(
    'HumanResources.Employee',
    'AK_Employee_NationalIDNumber'
)
WITH DENSITY_VECTOR;

現有索引的平均索引鍵長度為 21.66。該索引包含索引欄位 NationalIDNumber，以及作為資料列識別碼的叢集索引鍵；在此範例中，該欄位為 BusinessEntityID。

嘗試修改

CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_NationalIDNumber
ON [HumanResources].[Employee]
(
    NationalIDNumber ASC,
    JobTitle,
    HireDate
)
WITH DROP_EXISTING;

重新在跑一次之前的查詢，lookup 就會消失。

但是，如果我們再去查一次密度向量的時候

會發現從原先的21.66 爆增到最多 74.48，這代表每個 page 能容納的資料列數量會減少三倍，因此存取這個新索引的時候，所需要讀的 page 次數會增加。

所以效能好不好，不是看到 lookup 就直接瞎搞 include，妳要測試過才能說加 include 效能就會好。

除此之外，索引整體的行為也會因此改變。

建立涵蓋索引的另一種方式，是保留原本的索引鍵不變，僅使用 INCLUDE 子句，將所需欄位加入索引的葉節點層級

CREATE UNIQUE NONCLUSTERED INDEX AK_Employee_NationalIDNumber
ON [HumanResources].[Employee] (NationalIDNumber ASC)
INCLUDE
(
    JobTitle,
    HireDate
)
WITH DROP_EXISTING;

用這種方式去做的索引，執行計畫會跟上面那種方式做索引差不多，一樣都可以消除 lookup。

但是再回頭去看密度向量，可以看到平均長度回復到原本的數值，這是因為雖然我們把資料加入索引，但這些欄位只存在葉曾，並未成為索引 key 的一部分。

整體而言，這種設計在資料頁讀取方面應有較好的表現。不過，由於這個範例的索引鍵與資料集合都很小，因此不太可能在此看到明顯差異。

利用索引連結

這類執行方式相對少見，因此若把它當成主要的效能調校策略，可能會有問題。

SELECT poh.PurchaseOrderID,
       poh.VendorID,
       poh.OrderDate
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE VendorID = 1636
  AND poh.OrderDate = '2014/6/24';

614 微秒
10 次邏輯讀取

如同本章前面的其他查詢一樣，此查詢所使用的欄位並未完整包含在資料表現有的任何非叢集索引中。

這表示，即使 IX_PurchaseOrderHeader_VendorID 索引可以根據 WHERE 條件篩選資料，查詢中的其餘欄位仍必須從叢集索引中擷取。

如同前面所示，我們可以修改索引並加入其他欄位，使其成為涵蓋索引。然而，這樣做會改變索引本身：不是擴大索引鍵，就是在葉節點加入更多欄位，進而增加索引的整體大小。

但如果修改現有索引，反而對其他查詢造成負面影響，該怎麼辦？

那就參照下面這個做法，多一個索引讓執行計畫去做索引連結。

CREATE NONCLUSTERED INDEX IX_TEST
ON Purchasing.PurchaseOrderHeader (OrderDate);

4.66 微秒
4 次邏輯讀取

在這個執行計畫中，兩個索引都透過 Index Seek 進行資料搜尋。由於兩個 Seek 作業的資料都已排序，因此查詢最佳化工具使用 Merge Join，將兩邊唯一符合條件的資料列合併。

邏輯讀取次數從 10 次下降至 4 次，效能也有類似幅度的改善。

若將 IX_PurchaseOrderHeader_VendorID 修改為涵蓋索引，效能還會比這種作法更好，因為可以同時消除聯結作業，以及對第二個索引的讀取。不過，如前文所述，本例刻意避免修改既有索引。

Lookup 並不是免費的作業，因此在適當情況下，消除 Lookup 可以改善特定查詢的效能。分析 Lookup 本身並不困難，因為 Lookup 運算子會直接顯示所需資訊。接下來只需要從可行方案中選擇一種，解決該問題。