SQL SERVER 的臨界點（Tipping Point）

當一個索引搜尋無法涵蓋查詢所要求的欄位時，就會產生bookmark lookup（RID lookup / Key lookup），以取得查詢語句所需的欄位資料。

而bookmark lookup對效能而言，其實並不是一件好事。例如一個bookmark lookup的查詢，結果將返回10筆資料，就會形成10次的bookmark lookup，如果你用set statistics io on來觀察，這句查詢單是bookmark lookup就會產生10次的邏輯讀取，最後再加上原本的index seek的邏輯讀取，就是全部的邏輯讀取次數。

當bookmark lookup發生時，每返回一筆資料，就需要一次的邏輯讀取。

假設有個table，有3000筆資料，共佔用了100個page空間。如果是table scan的話，邏輯讀取次數也就是100。但如果是bookmark lookup，假設我們查詢的返回結果是200筆，就會造成大於200的邏輯讀取。這反而比table scan更慢更耗資源。

因此，SQLSERVER有個稱為「臨界點」（Tipping Point）的設置，當bookmark lookup產生時，查詢優化器會估計返回的筆數。當估計返回的筆數到達臨界點時，查詢優化器將直接採用scan的方式（table scan或clustered index scan）來執行此次查詢。

臨界點的計算是以table所佔用的page數來計算的：

臨界點 = 1/4 ~ 1/3 （total pages）

假設一個table共佔用100個page，那麼它的臨界點為：

100 * 1/4  ~ 100 * 1/3 = 25 ~ 33（臨界值範圍）

也就是當bookmark發生時， 如果估計查詢的筆數在大於25~33筆。就會改為scan的方式去讀取資料。

臨界點為什麼是一個範圍，而不是一個值呢。那是因為諸如索引碎片和陳舊的統計資訊，也可能影響優化器關於臨界點的決定。由於我們沒有優化器所具有的相同資訊，因此很難準確預測在特定的時間及特定的table的臨界點。這也是為什麼我們只能計算出一個可能的臨界值範圍。

底下我們做個實際的測試

drop table testlookup

go

create table testlookup(c1 int identity,c2 int ,c3 char(250))

go

insert into testlookup values(1,' This is a test string....')

go 3000

--建clustered index

create unique clustered index pk_1 on testlookup(c1)

go

--做假資料，將c2=c1，因為要建index

update testlookup set c2=c1 

go

create index idx1 on testlookup(c2)

go

Clustered Index共有2層，共101頁，如下圖

另外一個index(idx1)，也有2層，共7頁，如下圖

 

首先執行全表掃瞄

依前述的公式，這個table的臨界範圍應該會在：

101 * 1/4  ~ 101 * 1/3 = 25.25 ~ 33.33（臨界值範圍）

也就是說，當bookmark lookup查詢筆數到達這範圍(25.25 ~ 33.33)時，bookmark lookup，將會改為table scan。

執行條件式查詢，走IDX1的欄位，返回筆數估計為28筆。

從下圖中的執行計畫可以看到優化器選擇走bookmark lookup。

接下來我們將查詢條件放大，返回筆數估計29筆，可以看到優化器改走叢集索引掃瞄。而29這數字，確實落在臨界點範圍（25.25 ~ 33.33）中。

使用SQL Server時，經常會看到優化器做出與我所認知不相符的決定。在遇到這種情況時，我通常會去探究它背後的原因。

本篇所說的情況，也是我之前的困惑。我一直以為lookup再怎麼樣也應該會比scan快。現在回想起來，當時是基於對scan的誤解。以為只要看到scan，就表示有問題。但經過一番研究後，我發現事情並不是我本來所想像的那麼簡單。

應用不完全的知識常常導致不正確的結論。

~~共勉。

本篇是個人的心得，如果有錯誤的地方，也請大家不吝指導。