SQLSERVER wait type之SOS_SCHEDULER_YIELD

如同在「SQL Server的等待分析」中所說，SQLSERVER會給一個處於RUNNING的thread一個限量的時間（4ms），如果這個thread在這個限量的時間內沒有執行完畢。若這個thread不需等待任何資源，則會將這個thread退回到Queue中（RUNNABLE）,即使目前沒有其它的thread在等侯CPU也是一樣。

SOS_SCHEDULER_YIELD嚴格的來說，它並不是一種等待，它只是線程在4ms時間內，無法完成工作而將CPU讓出來，以避免其它線程「餓死」的一種調度做法，所以它不會顯示在sys.dm_os_waiting_tasks中。它與一般的resource wait不同，它被稱為signal wait。如果系統中沒有其它的線程在runnable中，通常它的signal wait time的時間會很短，也就是很快又會回到running的狀態。

當一個thread超過限量時間，退回到runnable時，將被標示「SOS_SCHEDULER_YIELD」，直到它回到running狀態。這段時間將被紀錄為「訊號等待時間signal wait time」。

如果一個命令執行，耗用的CPU時間不超過4ms，那麼就不會有SOS_SCHEDULER_YIELD的出現，如果你看到許多次的SOS_SCHEDULER_YIELD，表示你的系統有許多命令耗用超過4ms的CPU。

當SOS_SCHEDULER_YIELD的等待次數很高，但總的等待時間卻不高，整體而言CPU並不忙碌。這通常是無害的，僅代表有較多的命令不能在4ms的限量時間完成。例如DBA關閉了平行處理（MAXDOP=1）,也有可能造成此現象。

當SOS_SCHEDULER_YIELD的等待次數很高，而且總的等待時間也很高。這非常可能代表了CPU有壓力。表示你的系統有許多CPU密集型的查詢，例如許多複雜的SQL statement，如table scan、複雜或不符合SARG的查詢..等，你可能需要針對特定的語法或物件進行優化。如果伴隨著高CXPACKET的出現，則需要去調整平行處理的配置。（詳見：SQLSERVER wait type之CXPACKET）

當SOS_SCHEDULER_YIELD的等待次數不高，但總的等待時間卻很高。這種情況表示你SQLSERVER所在的主機，作業系統的其它程式在搶佔CPU的資源。如果你在同一台主機上同時安裝了SQLSERVER和AP伺服器（或其它非DB相關的軟體），就有可能發生這種現象。或者在作業系統開啟了省電功能，也可能導致此種現象。

SQLSERVER wait type之RESOURCE_SEMAPHORE

RESOURCE_SEMAPHORE

當SQL Server收到用戶查詢時，它首先建立一個已編譯(complied plan)的計劃，然後根據已編譯的計劃建立一個執行計劃(execution plan)。

當SQL Server建立一個已編譯的計劃時，它會計算兩個記憶體授予參數(memory grant parameters)，稱為「所需記憶體required memory」和「額外記憶體additional memory」。所需記憶體是運行排序和雜湊連接(sort and hash join)所需的最小記憶體。這是必需的，因為如果沒有這個記憶體可用，查詢將不會啟動。

額外的記憶體是將臨時的資料行(temporary rows)存儲在記憶體中所需的記憶體量。這被稱為額外的，如果沒有足夠的可用記憶體，則可以將查詢存儲在磁碟上。

首先，SQLSERVER計算這個查詢執行需要多少記憶體。這通常是所需記憶體和額外記憶體的總和，但如果您的查詢正在使用平行處理，則所需的記憶體將為（所需記憶體* DOP）。再加上「額外記憶體」。這被稱為請求的記憶體(requested memory)。

SQL Server中有一個稱為RESOURCE SEMAPHORE的內部設備，它被用來授予請求的記憶體給每個查詢。如果記憶體不足以授予時，那麼該查詢將處於等待記憶體的狀態，其等待類型即為「RESOURCE_SEMAPHORE」。

我們可以通過SELECT * FROM sys.dm_exec_query_resource_semaphores

來查詢每個「資源集區resource pool」已授予或未授予的記憶體大小資訊。

resource_semaphore_id為 0，主要用於「一般查詢」。resource_semaphore_id為1，則用於小型查詢（所謂小型查詢是指請求的記憶體 ﹤5MB，且查詢成本 ﹤3）

，上圖中可以看到有100個查詢正在等待記憶體的授予。

通過SELECT * FROM sys.dm_exec_query_memory_grants可以查詢每個查詢的記憶體授予的詳細資料

grant_time及granted_memory_kb為NULL，表示正在等待授予記憶體。

我們也可以通過這些資訊，找到最耗用記憶體的查詢，並且透過上面指令所得到的sql_handle欄位值，去取得SQL語法。

RESOURCE_SEMAPHORE_QUERY_COMPILE

另外有個等待類型「RESOURCE_SEMAPHORE_QUERY_COMPILE」主要是在編譯執行計畫時，編譯這個動作也需要記憶體。

SQLSERVER將編譯可用的記憶體分為三級（3個gateway）「small、medium、big」，剛開始都是給予small gateway級別的記憶體，但如果一個複雜的語法，small記憶體無法滿足編譯所需，則會要求進入「更上一級」記憶體授予，直至big gateway。

每一級gateway可容許同時編譯的數量是有限制「Configured Unit」的，如果更上一級的gateway，因為限額已滿無法授予記憶體給此編譯動作，那麼將進入「等待」，此時的wait type即為「RESOURCE_SEMAPHORE_QUERY_COMPILE」。

SQLSERVER內建了3個編譯所需記憶體的關卡(gateway)「small、Medium、Big」，可以透過dbcc memorystatus得知。

1.small gateway：

configure unit 通常是cpu core*4。

2.Medium gateway：

configure unit 通常是cpu core*1。

3.Big gateway：

configure unit 通常是1。

Summary

RESOURCE_SEMAPHORE

表示目前記憶體可用空間不足以執行SQL語法。

解決方案：

一、調整SQL語法，特別是（order by或hash join）。

二、擴充記憶體大小。

三、調降MAXDOP。

四、清除或搬移舊資料至歷史區，降低ONLINE資料量。

RESOURCE_SEMAPHORE_QUERY_COMPILE

表示SQL語法過於複雜，或者複雜的SQL查詢太密集，系統Memory Gateway不足以編譯這些SQL。

解決方案：

一、調降SQL語法的複雜度。

二、增加CPU的core數。

三、避免過度使用WITH RECOMPILE。

SQLSERVER wait type之CXPACKET

CXPACKET這種等待類型(wait type)，是SQLSERVER最常見的等待類型之一。

CXPACKET這個詞來自於「Class Exchange Packet」，可以把它解釋為平行線程(thread)間的資料交換。

當SQLSERVER決定使用平行處理來執行一句SQL statement時，會啟動一個協調控制線程（coordinator/control thread），線程編號為0，即thread 0，接著視所能使用的CPU核心數，分別啟動一個線程。

協調線程負責各線程間的協調，不參與SQL的執行。其餘的thread則按分配的工作同時執行。各個線程執行的時間有快有慢，先執行完的線程，需等待其它線程執行完成，這時的「等待」類型，即為CXPACKET。如下圖所示：

上圖各threads暗紅色部份的時間加總，即為執行此SQL statement的CXPACKET等待時間。

thread 0的CXPACKET的等待時間，其實就代表了整個SQL statement執行的時間。因此在平行處理的計畫中，CXPACKET是必然會發生的，即使各線程都同時完成。

通常的建議是「只要CXPACKET總時間不超過總等待時間的50%」，則不應把它視為一個問題而是一個指標。

我們可以使用下列語法，查詢各等待事件所佔的時間、佔比、累進佔比

WITH Waits AS

 (

 SELECT 

   wait_type, 

   wait_time_ms / 1000. AS wait_time_s,

   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

 FROM sys.dm_os_wait_stats

 WHERE wait_type 

   NOT IN

     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',

   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',

   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')

   ) -- filter out additional irrelevant waits

   

SELECT W1.wait_type,

 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, 

 W1.wait_type, 

 W1.wait_time_s, 

 W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

CXPACKET雖然是因平行處理而產生，但產生高的CXPACKET並不一定是「平行處理的問題」，常常它代表了其它問題的症狀。

例如CXPACKET如果伴隨著高的PAGEIOLATCH_*（可能還會見到IO_COMPLETION, ASYNC_IO_COMPLETION），這很大的背後原因是表示I/O子系統無法滿足平行處理的需求。這種情況下，你如果去調整最大並行度，或許會看到CXPACKET降低了，但其實你的系統效能（AP執行的反應時間）並不會得到提昇，問題沒有得到根本的解決。

這種情況，應首先考慮I/O的優化。

當然，直接去昇級I/O的硬體（例如換成SSD）也是一種方法，只是這有點強人所難了；我們可以針對相關的SQL語法/Table/index等進行優化（例如索引未匹配、碎片整理..等），以儘量減少I/O，然後再做觀察。

另外，是否會採用平行處理，以及平行處理的各個工作線程（如上圖的thread 1~4）的工作量分配，均是由系統依據統計值計算而來。如果統計值是正確的，那麼平行處理各工作線程的工作分配通常會較為平均，其CXPACKET的等待總時間應該不會太高。如果統計值過期，那就有可能不當的啟用平行處理，或者造成各線程工作分配不均，形成較大的CXPACKET等待。面對CXPACKET，也可以先嘗試由統計值更新著手。

當高CXPACKET伴隨著高SOS_SCHEDULER_YIELD及LACTH_*的時候，這種情況表示「平行處理」確實是問題了，這時候你可能需要去調整系統的平行處理組態。

SQL SERVER的平行處理程度，主要受下列兩個選項的控制

cost threshold for parallelism （平行處理原則的成本臨界值）

SQL SERVER執行SQL前，先估計SQL所需的時間成本（單位為秒），如果大於上述臨界值（預設值為5），那麼就會採用平行處理來執行SQL語法。這個值該不該調整，該調成多少？讀者們可以參考下列兩篇文章（感謝PASS的好友Rock提供）：

https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/

max degree of parallelism （平行處理原則的最大程度）

如果SQL SERVER決定採用平行處理，將依照max degree of parallelism選項所設定的值（threads）同時執行。0：代表無限制（最大限度）。

max degree of parallelism要設定為多少才是適當的，這個有許多種說法。主要還是要看實際的系統狀況而定。底下的表格僅為個人經驗的建議值。

上面的表格只是我個人的初始性建議，之後最好視系統實際的情況，觀察相關的效能參數，做出符合實際環境的調整。

SQL Server 的 Waits

SQL命令(SQL Statement)是由CPU來執行，在執行的過程中，難以避免的會因為資源的調用、鎖定…等的因素，造成執行過程中可能發生多次的「等待（waits）」。因此一個命令的執行，從開始到完成，其回應時間，可以簡單的用下列公式來表示：

response time = service time（CPU） + total wait time

回應時間 = 服務時間 + 合計等待時間（服務時間指CPU真正用於執行命令的時間）

SQLSERVER效能調校，最終無非是為了加快回應速度，降低回應時間。一個命令的執行，要不就是在CPU中運算，要不就是在「等待（Waits）」。瞭解系統到底在等什麼，是系統效能問題處理的基本。但它卻不常被人理解並且應用於效能問題排除。

我想先就個人所知，解釋一下SQLSERVER命令執行過程的狀態變化..

一個thread在未執行任何命令時，其狀態(status)會是sleeping。當執行命令時，系統會分配CPU資源給thread執行命令。當一個thread正在使用CPU時，狀態為RUNNING。直到它需要等待資源（如I/O，或將頁面從磁碟中讀取到buffer pool中，或遇到block需等待…），它會被移動到一個無序的waiter list中，此時thread狀態將被標示為SUSPENDED。一個狀態為SUSPENDED的thread，代表它正在等候某個資源。

另外在系統中有個FIFO（先進先出）的隊列（Queue），其中存放著所有在等待CPU的thread，這些存在於Queue中的thread，其狀態為RUNNABLE。

當一個thread由於執行過程需要等待其它資源，將會被移至「waiter list」，並將其狀態標示為SUSPENDED，於此同時，處於隊列中(Queue)的下一個thread被賦予CPU並且變為RUNNING。如果SUSPENDED列表上的thread被通知資源可用，則它將變為RUNNABLE，並放在RUNNABLE隊列的後面，加入排隊。整個執行過程是螺旋狀的從RUNNING到SUSPENDED到RUNNABLE，直到任務完成為止。

SQLSERVER會給一個處於RUNNING的thread一個限量的時間（4ms），如果這個thread在這個限量的時間內沒有執行完畢。如果這個thread不需等待任何資源，則會將這個thread退回到Queue中（RUNNABLE）,即使目前沒有其它的thread在等侯CPU也是一樣；然後系統會從Queue中取得下一個thread並執行。

如果一個thread在4ms的限量執行時間內，因等待資源而無法繼續執行，那麼它會被標示為SUSPENDED並移到waiter list中，之後SQLSERVER一樣會從Queue中取得下一個thread執行。當那個等待的資源通知完成後，這個waiter list的thread將被移至Queue隊列中等待.

一個thread：

在RUNNING所花費的時間（稱為：執行時間service time）

在RUNNABLE隊列上等待的時間（稱為：信號等待時間signal wait time）

在SUSPENDED列表中等待的時間（稱為：資源等待時間resource wait time）

SQLSERVERR會永久性的跟蹤程紀錄執行過程中的「等待」資訊，包含「等待什麼(wait type)」及「等待時間(wait time)」

一個thread的等待時間可以用下列公式表示：

total wait time = resource wait time + signal wait time

一個thread的回應時間可以用下列公式表示：

response time = service time + total wait time

可以透過sys.dm_os_waiting_tasks或sys.dm_exec_requests中查詢thread目前在等待什麼（wait type）及等待時間（wait time）。當這個wait event結束後，該等待的資訊會從上述兩個DMV中移除（在sys.dm_exec_requests中會被轉移成lastwaittype），並將這個等待的型態及時間，加入sys.dm_os_wait_stats的統計資料中。

註：SOS_SCHEDULER_YIELD不會顯示在sys.dm_os_waiting_tasks中。

而任何的等待歷史，均會被紀錄在sys.dm_os_wait_stats。透過這個DMV，可以查詢各種等待類型（wait type）的累積等待時間，包含了訊號等待及資源等待。

sys.dm_os_wait_stats會在SQLSERVER啟動時歸零，或者我們也可以透過下列指令將其歸零：

dbcc sqlperf('sys.dm_os_wait_stats', clear)

在進行系統層級效能診斷時，可以查詢sys.dm_os_wait_stats，以取得系統自上次歸零以來至今的等待情況統計，根據這些最常發生的等待資訊，可以瞭解系統是否有資源使用上的瓶頸，並進行相對的優化。

在進行會話層級（session level）的效能診斷時可以使用sessiod_id查詢sys.dm_os_waiting_task或者sys.dm_exec_requests，以取得「目前」session的等待資訊。不過由於wait event結束後，該等待資訊即會從上述DMV中移除，所以只能看到目前的等待資訊（或者最後一次的等待資訊）。

註：SQLSERVER 2016有一個新的DMV「sys.dm_exec_session_wait_stats」，可以紀錄session的各種等待資訊的彙總。

在SQLSERVER 2016以前的版本，如果要知道session在執行命令過程中，究竟遇到了哪些等待，最佳方法是透過extented event來擷取session執行過程中的等待資訊，詳細的script，可以參考下列網址：

https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/

回應時間 = 服務時間 + 合計等待時間（服務時間指CPU真正用於執行命令的時間）

瞭解Waits是SQLSERVER效能調校重要的一環，但它卻經常被人所忽略。我個人的學習歷程是先知道waits的一些概念後，再逐步的去瞭解一些重要的wait type及其背後的含義，在面臨SQLSERVER系統效能問題時，可以先查出系統的等待統計資訊，如果有正確的理解，這些等待資訊將會告訴你系統瓶頸的所在。

SQLSERVER隨著版本的昇級，其內建的等待類型(wait type)數目也跟著增加，以SQL2012而言，應該有5-6佰個之多，我們很難每個等待類型都瞭解。但有些重要且常見的等待類型，建議從事DBA工作者們必需要知道，並且最好能深入的理解。

索引前導鍵(Leading-column)的選擇

如果有A、B、C三個欄位要據以建立一個索引，那麼建立的方式共有六種：

create index idx1 on TABLE(A,B,C)

create index idx1 on TABLE(A,C,B)

create index idx1 on TABLE(B,A,C)

create index idx1 on TABLE(B,C,A)

create index idx1 on TABLE(C,A,B)

create index idx1 on TABLE(C,B,A)

~~你會用哪一種？

~~索引鍵的順序不同，對搜尋效率有影響嗎？

~~如果有影響，那麼你的考量原則是什麼？

早期的我，由於不知道有什麼原則可以遵循，因此當時我會將每個索引都建一次，然後執行相應的SQL語句，觀察所耗用的時間及執行計畫的成本，最後選擇耗用時間或成本最少的索引。

~~我想大多數的DBA應該跟我一樣，曾經面臨這樣的問題~~

「索引」是排序過的資料，透過索引可以加快資料的搜尋速度。當索引建立的正確並有適當的維護時，它可以顯著的提高SQL語法執行的效能。索引可以想像成一般書籍的目錄，有這種目錄，你就可以快速的找到你要找的主題。如果沒有，那麼你可能要翻遍整本書才能找到你所需要的訊息。

一般會考量匹配前端AP常用SQL語法來據以建立索引，以加快SQL語法的執行速度。如果SQL語法的where子句只有一個欄位查詢條件，那麼就沒有鍵值順序的問題，只有這個欄位適不適合單獨建立索引的問題。

如果SQL語法的where子句有多個欄位查詢條件，通常我們會用這些欄位來建立一個「複合式索引」。

複合式索引，由多個欄位組成，這些欄位在索引中的先後順序，常見的建議是以每個欄位的「選擇性(Selectivity)」來決定其排列的順序。選擇性越高的，排在越前面。這樣的原則在B-Tree的搜尋上，可以最大程度上的過濾掉不需要的分支。加快葉層級掃瞄定位的速度。

「選擇性」可以定義為：「不同紀錄與總紀錄的百分比」。例如：table共有1000筆資料，其中有個性別欄位[Gender]，它的欄位值只有兩種「男、女」，所以Gender欄位的選擇性等於2/1000=0.002。

選擇性越高，表示資料重覆的比例越低。

一個欄位的選擇性，可以簡單地使用下列SQL語法得知

select convert(float,count(distinct 欄位名))/count(*) from TABLE

舉個簡單的例子，假設有一句經常執行的SQL如下

select count(*) from TABLE where LoginDate='2017-03-17' and LoginName='Phil'

如果要匹配這句SQL語法建立index，有兩種建法

(1)  Create index idx1 on TABLE(LoginDate,LoginName)

(2)  Create index idx1 on TABLE(LoginName,LoginDate)

假設這兩個欄位的選擇性：

LoginDate：0.02

LoginName：0.0004

LoginDate的選擇性高於LoginName，因此(1)，會是我們建立索引的較佳選擇。

在複合式索引中，除了欄位的排列順序外，對於索引前導鍵（leading-column）的選擇尤為重要。那是因為在一個多欄位組成的索引中，只有第一個欄位是有序的。

索引的前導鍵必需出現在where條件子句中，否則這個索引是無法被搜尋的。

例如上面的索引：

Create index idx1 on TABLE(LoginDate,LoginName)

如果查詢語句為：

select count(*) from TABLE where LoginName='Phil'

索引前導鍵沒有出現在where條件中，這句查詢將無法使用到這個索引的搜尋。

~~然而，僅只考慮欄位的「選擇性」，並不足以決定最佳的索引策略

where條件子句，是由一些「比較運算符」組成，如「=、<>、between、like」等等，我們可把這些比較運算符歸類為兩種

(1) 等式（equality）：即「=」

(2) 非等式（inequality）：凡不是「=」者，皆為非等式。

我們再看下面的例子

select * from TABLE where ID between 'Axxxxx' and 'Bxxxx' and Gender='M'

ID：身份證號

Gender：性別

查詢身份證號在某一範圍內的男性。其中ID欄位為「非等式(inequality)」，而Gender欄位為「等式(equality)」

若要匹配此語法建立叢集索引，有兩種建法

create unique clustered index idx1 on TABLE(ID,Gender)  →身份證欄位在前

VS

create unique clustered index idx1 on TABLE(Gender, ID)  →性別欄位在前

假設TABLE有20萬筆紀錄，身份證號在此TABLE中是唯一的，那麼這兩個欄位的選擇性如下：

(1) ID：1

(2) Gender：2/200000=0.00001

在建立索引時，很自然地，我們會選擇ID做為前導鍵：

create unique clustered index idx1 on TABLE(ID,Gender) →身份證欄位在前

但是，只考慮選擇性，而不考慮where條件是「等式(equality)」或「非等式(inequality)」，這樣的索引真的會比較有效率嗎？

我們透過少量的資料，來解釋一下….

Table「TestIndex」，假設有10筆資料，我們對它執行一個查詢：

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'

分兩種情況來看

一、以ID,Gender來排序

如下表所示，在B-Tree葉層級(leaf level)共需掃瞄5筆資料後，可得到確定的結果

二、以Gender,ID來排序

如下表所示，在B-Tree葉層級(leaf level)共需搜尋比對3筆資料後，即可得到結果

~~這代表著什麼意思呢？

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'     

首先在這句查詢條件中，ID是「非等式(inequality)」，Gender是「等式（equality）」

SQLSERVER的索引結構，使用「查詢條件值」，與B-Tree的根節點及中間節點內的鍵值進行比對，逐層確定要掃瞄範圍。當到達葉層級（leaf level）後，即開始進行掃瞄，若一頁掃瞄完成尚未超過條件值的範圍，則根據頁鏈再往下一頁掃瞄，直到發現第一筆超過範圍的紀錄為止。

多重欄位的複合式索引，一般的建議是將「選擇性」最高的欄位放在最前面做為前導鍵，這個主要是為了在根節點及中間節點的比對上，消除更多的分支，這會節省一些時間在確定掃瞄範圍上。

但一旦到達leaf level開始掃瞄，如果前導鍵是「非等式inequality」，可能導致在葉層級的掃瞄範圍擴大（如上面少量資料的說明）。

使用選擇性高的「非等式inequality」欄位做為索引的前導鍵，雖然可以加快「掃瞄範圍定位」的時間，但也會使得在葉層級的掃瞄頁數擴大許多。

掃瞄範圍的定位在根節點及中間節點中確定，即使是在大型的索引，可能也只有6~7個層級，因此它定位節省的成本有限。

反而是在葉層級的資料掃瞄上，使用「非等式inequality」做為索引的前導鍵，有可能擴大掃瞄的範圍成百上千頁。因此在索引建立時，關於葉層級的掃瞄成本應列為優先考量。

以下截錄一段SQLCAT Team的說明，請參考

However, once the leaf level is reached and scanning begins on the requested range, placing the inequality column first can result in the reading of hundreds or even thousands of extra pages to satisfy the same query. Clearly, the tradeoff at the leaf level can quickly overwhelm the advantages gained at the root and intermediate levels on queries that return ranges of values.

Google英翻中（有修飾）：

一旦達到葉級並且開始掃描所需要的範圍，則不等式欄位(inequality column)為前導鍵，可以導致讀取數百甚至數千個額外的頁面，以滿足相同的查詢。 顯然，在葉級別上的權衡可以很快地壓倒根節點和中間層節點對返回符合範圍的查詢所獲得的優勢。

多重欄位索引，使用「等式(equality)」欄位為前導鍵，會比用「非等式(inequality)」欄位做為前導鍵更有效率。即使等式的欄位選擇性非常的低，即使它是像「性別」這種只有兩種資料的欄位。

多重欄位索引鍵的考量優先順序應為：

(1)等式(equality)欄位

(2)非等式(inequality)欄位

(3)若等式（或非等式）欄位有多個，則使用這些欄位的選擇性由高到低順序排列。

因此，在上面的例子中

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'  

若要建立複合式(叢集或非叢集)索引以匹配這樣的查詢，應該以等式欄位Gender為前導鍵：

create unique clustered index idx1 on TestIndex(Gender,ID)

==========================================================

我們來做一個實驗，以實做來瞭解上面的說法並確認其是否正確…

首先建立一個 Table

身份證號ID，用identity來模擬，Gender為性別，另加一個固定長度的住址欄位，目地是讓一筆資料夠長，令整個Table多一些pages，以利評估。

create table TestIndex(ID int identity ,Gender char(1),Addr char(200))

新增男女各10萬筆資料

declare @x int =1

while (@x<=100000)

  begin

    insert into TestIndex values('M' , 'This is a Address...')

    insert into TestIndex values('F' , 'This is a Address...')

       set @x=@x+1

  end

首先執行一次全表掃瞄查詢

set statistics io,time on

select count(*) from TestIndex

資料表 'testindex'。掃描計數 1，邏輯讀取 5406，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

SQL Server 執行次數:

，CPU 時間 = 94 ms，經過時間 = 113 ms。

上面執行後的統計結果意思是，SQLSERVER完成此次查詢，總共讀取了5406頁，花費CPU時間94 ms，總花費時間（含IO等）113 ms。

首先我們建立叢集索引，使用選擇性高但條件是inequality的欄位「ID」做為前導鍵

create unique clustered index C_idx_ID on TestIndex(ID,Gender)

執行條件式查詢

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果中可以看到，索引使用ID做為前導鍵，邏輯讀取為10。

接著我們再來測試，使用性別做為前導鍵

建立叢集索引，使用選擇性低，但條件是equality欄位「Gender」做為前導鍵

drop index TestIndex. C_idx_ID

create unique clustered index C_idx_Gender on TestIndex(Gender,ID)

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果來看，反而是選擇性低的「性別」欄位在前的索引，效能更好。邏輯讀取由10降到了7。

註：

通常我們使用「邏輯讀取」來衡量SQL的效能，它越小越好。我們不會使用CPU Time或elapsed time（經過時間），因為這兩個時間值有可能是「不可測量的小(immeasurably small)」，例如你可能經常看到CPU time = 0 ms。

另外一個elapsed time還包含了I/O子系統所花費的時間，而I/O子系統有可能因為其它因素忙碌，使得elapsed time是會變動的，故不適合做為評估基準。

使用「邏輯讀取」數，可以衡量同一句SQL走不同index的成本以評估哪個index更佳。或者用在索引碎片重整前後或統計值更新前後的效能指標。

從上面的實驗，我們初步證明了：

多重欄位索引，使用「等式(equality)」欄位為前導鍵，會比用「非等式(inequality)」欄位做為前導鍵更有效率。

~~然而SQLSERVER的優化器，它知道這個原則嗎？

我們再來測試

刪除叢集索引

drop index TestIndex.C_idx_Gender

分別使用ID及Gneder做為前導鍵，建立兩個非叢集索引

create index idx_ID on TestIndex(ID,Gender)

create index idx_Gender on TestIndex(Gender,ID)

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果的執行計畫中可以看到，系統優化器選擇走「idx_Gender」這個索引，而這索引是用equality、選擇性低的性別(Gender)欄位為前導鍵的。這表明SQLSEVER優化器知道equality欄位為前導鍵的索引是更好的選擇。

~~接下來，我們感興趣的是，SQLSERVER是如何地判斷哪個index是更好的選擇?

反對使用低選擇性的欄位做為索引前導鍵的主要理由是：「統計資料只會針對索引的第一個欄位建立統計訊息」，如果前導鍵只有男、女兩種選擇，那麼據以建立的統計訊息，是不太具有參考價值的。

對於「直方圖(Histogram)」而言，這種說法是正確的。

但在多where條件下，優化器評估要走哪個index，直方圖並不是唯一的參考因子。如「身份證號」跟「性別」的例子，這個索引無論哪個欄位當做前導鍵，SQLSERVER仍然會在產生執行計畫時，評估第二個欄位的統計訊息。

~~我們以上面的範例，繼續說明

首先刪除相關的index及該table的所有統計訊息，並重建相同的兩個索引

create index idx_ID on TestIndex(ID,Gender)

create index idx_Gender on TestIndex(Gender,ID)

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

上圖可以看到目前textindex這個Table，有兩組統計資料。

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

優化器選擇走idx_Gender，而且有正確估計到資料列數目為100。

檢查索引的使用狀況

檢查索引的使用狀況，我們確認只有idx_Gender被seek過1次，另一索引idx_ID並沒有被使用過。

再次查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

執行完查詢後，還是一樣，有兩組統計資料。

  

我們檢查一下idx_Gender的統計資料

dbcc show_statistics(TestIndex,idx_Gender)

看紅框處，系統確實僅使用前導鍵「Gender/性別」做為直方圖。在這個直方圖中，只有F跟M兩種資料。每種資料各有10萬筆。

~~~那麼，問題來了~~

系統走idx_Gender這個索引，依照這索引的統計資料，系統是憑什麼「估計」出符合條件的有100筆 .！？?

我們先刪除掉另一個索引

drop index testindex.idx_ID

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

確定只剩下一組統計資料

  

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

我們可以發現，系統仍然選擇使用相同的索引idx_Gender，但「估計」的資料列數目，卻由原本的「100」變成了「1」。這個估計值跟實際的資料列數目，誤差頗大。

執行計畫所使用的index沒變，但估計的資料列數目變了。這表示某個用於產生執行計畫的統計資料發生了改變。而這個改變了的「統計資料」，必然不是idx_Gender這個索引的統計值。而是另有其它。因為idx_Gender並沒有任何的變動。

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

我們可以看到，在執行查詢後，系統竟然自動增加了一組統計資料「auto_created = 1」。

我們檢查一下，看看這個新增的統計資料，它到底統計了什麼

dbcc show_statistics(TestIndex,_WA_Sys_00000001_744F2D60)

由上圖可以發現，系統自動產生了「ID」這個欄位的統計資訊。

自動產生的統計資料跟建立索引時產生的統計資料，兩者最大的不同點在於自動產生的統計資訊係採取抽樣方式建立，所以統計資料會有較大誤差（上圖共有200000筆資料，抽樣數為54954）。

而建立索引時所產生的統計資訊則是fullscan，故不會有誤差。

之所以會自動產生統計資料，是因為查詢的語句中，有用到ID欄位為條件，但優化器搜遍TABLE所有的索引，找不到ID欄位的直方圖(histogram)統計值，系統便會去檢查資料庫的「自動建立統計資料」的選項是否為true（如下圖），若是true，則自動產生ID的統計資料。

我們手動更新一下自動建立的統計訊息

update statistics testindex _WA_Sys_00000001_744F2D60 with fullscan

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

更新自動建立的統計資料後，可以發現估計的資料列數目回到正常。

綜上所述，多重欄位索引中無論哪個欄位做為前導鍵，SQL Server在確定使用哪個索引之前，會以SQL語法的where條件，去評估每個欄位的統計資料。僅僅只有前導鍵的統計訊息並不足夠。會在產生查詢計劃時搜尋其它欄位的統計資訊。

SQL Server首先依where條件的欄位，查看有沒有索引是用這些欄位當前導鍵的。如果有，SQL Server將會使用該索引的統計資料。接著搜尋該索引有無下一個where條件欄位的統計訊息。如果沒有，則檢查其它索引是否包含這欄位的統計訊息，如果也找不到，且該資料庫已啟用了AUTO_CREATE_STATISTICS。就會自動建立該欄位的統計訊息，並且在產生查詢計劃時使用這些自動創建的統計資料。

  

結論

在「自動建立統計資料」及「自動更新統計資料」兩選項為true的前提下。

多重欄位索引的建立，僅僅檢查欄位的基數或選擇性，並不足以決定最佳的索引策略。

當SQL語法的查詢條件，包含等式(equality)和非等式（inequality）的欄位時，應首先考慮等式(equality)欄位 - 即使這個欄位的選擇性非常的低。即使這個欄位像是性別欄位（只有兩種型態資料）也是如此。

請注意，將非等式（inequality）欄位做為前導鍵，永遠不會比等式(equality)欄位更有效。

索引鍵欄位的排列順序，考量因素應為：

(1)等式(equality)欄位

(2)非等式(inequality)欄位

(3)若等式（或非等式）欄位有多個，則使用這些欄位的選擇性由高到低順序排列。

寫在最後

我記得有人說過，DBA依工作性質，可以分成兩類：

一、AP-DBA：主要是與AP開發人員協同工作，經常與開發人員接觸。

二、DB-DBA：專職負責維持並保障DB的穩定服務，較少與AP接觸。

如果你是一位所謂的AP-DBA，那麼理解及掌握索引的知識，將非常有助於工作的推展。

實務上，AP所執行的SQL語法，除了where條件外，還經常會包含有group by/order by..等條件。而本篇，主要著重在索引「前導鍵」的選擇，對於當where / group /order 同時出現時的索引策略，以及clustered vs non-clustered index或key column vs include column等議題，則不在討論範圍。

索引是影響資料庫效能的重要關鍵因素，但卻少有書本或文章去談論它建立時應考量的原則及策略。這篇文章主要在說明複合式索引的欄位順序該如何決定，特別著墨在前導鍵的選擇上。文中做了比較多的實驗，或許讀起來比較凌亂，但其中包含了一些重要的觀念。

我自己跌跌撞撞的摸索多年，才算是對它有初步的瞭解。

希望這篇文章有助於跟我一樣正在DBA這條路上前行的朋友們，在面對多重欄位索引時，不會再猶疑不定，也可以少走一些彎路。