MS SQL Server 數(shù)據(jù)庫的任務(wù)調(diào)度機(jī)制詳細(xì)介紹

sql server OS是在Windows之上，用于服務(wù)SQL Server的一個(gè)用戶級(jí)別的操作系統(tǒng)層次。它將操作系統(tǒng)部分的功能從整個(gè)SQL Server引擎中抽象出來，單獨(dú)形成一層，以便為存儲(chǔ)引擎提供服務(wù)。SQL Server OS主要提供了任務(wù)調(diào)度、內(nèi)存分配、死鎖檢測(cè)、資源檢測(cè)、鎖管理、Buffer Pool管理等多種功能。本篇文章主要是談一談SQL OS中所提供的任務(wù)調(diào)度機(jī)制。

搶占式(Preemptive)調(diào)度與非搶占式(non-Preemptive)調(diào)度

數(shù)據(jù)庫層面的任務(wù)調(diào)度的起源是ACM上的一篇名為“Operating System Support for Database Management”。但是對(duì)于Windows來說，在操作系統(tǒng)層面專門加入支持?jǐn)?shù)據(jù)庫的任務(wù)調(diào)度，還不如在SQL Server中專門抽象出來一層進(jìn)行調(diào)度，既然可以抽象出來一層進(jìn)行數(shù)據(jù)庫層面的任務(wù)調(diào)度，那么何不在這個(gè)抽象層進(jìn)行內(nèi)存和IO等的管理呢？這個(gè)想法，就是SQL Server OS的起源。

在Windows NT4之后，Windows任務(wù)調(diào)度是搶占式的，也就是說Windows任務(wù)是根據(jù)任務(wù)的優(yōu)先級(jí)和時(shí)間片來決定。如果一個(gè)任務(wù)的時(shí)間片用完，或是有更高優(yōu)先級(jí)的任務(wù)正在等待，那么操作系統(tǒng)可以強(qiáng)制剝奪正在運(yùn)行的線程（線程是任務(wù)調(diào)度的基本單位）所占用的CPU，將CPU資源讓給其它線程。

但是對(duì)于SQL Server來說，這種非合作式的、基于時(shí)間片的任務(wù)調(diào)度機(jī)制就不那么合適了。如果SQL Server使用Windows內(nèi)的任務(wù)調(diào)度機(jī)制來進(jìn)行任務(wù)調(diào)度的話，Windows不會(huì)根據(jù)SQL Server的調(diào)度機(jī)制進(jìn)行優(yōu)化，只是根據(jù)時(shí)間片和優(yōu)先級(jí)來中斷線程，這會(huì)導(dǎo)致如下兩個(gè)缺陷:

Windows不會(huì)知道SQL Server中任務(wù)(也就是SQL OS中的Task,會(huì)在文章后面講到)的最佳中斷點(diǎn)，這勢(shì)必會(huì)造成更多的Context Switch（Context Switch代價(jià)非常非常高昂,需要線程字用戶態(tài)和核心態(tài)之間轉(zhuǎn)換），因?yàn)閃indows調(diào)度不是線程本身決定是否該出讓CPU，而是由Windows決定。Windows并不會(huì)知道當(dāng)前數(shù)據(jù)庫中對(duì)應(yīng)的線程是否正在做關(guān)鍵任務(wù)，只會(huì)不分青紅皂白的奪取線程的CPU。

連入SQL Server的連接不可能一直在執(zhí)行，每一個(gè)Batch之間會(huì)有大量空閑時(shí)間。如果每個(gè)連接都需要單獨(dú)占用一個(gè)線程，那么SQL Server維護(hù)這些線程就需要消耗額外的資源，這是很不明智的。

而對(duì)于SQL Server OS來說，線程調(diào)度采用的合作模式而不是搶占模式。這是因?yàn)檫@些數(shù)據(jù)庫內(nèi)的任務(wù)都在SQL Server這個(gè)SandBox之內(nèi)，SQL Server充分相信其內(nèi)線程，所以除非線程主動(dòng)放棄CPU，SQL Server OS不會(huì)強(qiáng)制剝奪線程的CPU。這樣一來，雖然Worker之間的切換依然是通過Windows的Context Switch進(jìn)行，但這種合作模式會(huì)大大減少所需Context Switch的次數(shù)。

SQL Server決定哪一個(gè)時(shí)間點(diǎn)哪一個(gè)線程運(yùn)行，是通過一個(gè)叫Scheduler的東西進(jìn)行的，下面讓我們來看Scheduler。

Scheduler

SQL Server中每一個(gè)邏輯CPU都有一個(gè)與之對(duì)應(yīng)的Scheduler，只有拿到Scheduler所有權(quán)的任務(wù)才允許被執(zhí)行，Scheduler可以看做一個(gè)隊(duì)SQLOS來說的邏輯CPU。您可以通過sys.dm_os_schedulers這個(gè)DMV來看系統(tǒng)中所有的Scheduler，如圖1所示。

圖1.查看sys.dm_os_schedulers

我的筆記本是一個(gè)i7四核8線程的CPU，對(duì)應(yīng)的，可以看到除了DAC和運(yùn)行系統(tǒng)任務(wù)的HIDDEN Scheduler，剩下的Scheduler一共8個(gè)，每個(gè)對(duì)應(yīng)一個(gè)邏輯CPU，用于處理內(nèi)部Task。當(dāng)然，您也可以通過設(shè)置Affinity來將某些Scheduler Offline，如圖2所示。注意，這個(gè)過程是在線的，無需重啟SQL Server就能實(shí)現(xiàn)。

圖2.設(shè)置Affinity

此時(shí)，無需重啟實(shí)例就能看到4個(gè)Scheduler被Offline,如圖3所示:

圖3.在線Offline 4個(gè)Scheduler

一般來說，除非您的服務(wù)器上運(yùn)行其他實(shí)例或程序，否則不需要控制Affinity。

在圖1中，我們還注意到，除了Visible的Scheduler之外，還有一些特殊的Scheduler，這些Scheduler的ID都大于255，這類Scheduler都用于系統(tǒng)內(nèi)部使用，比如說資源管理、DAC、備份還原操作等。另外，雖然Scheduler和邏輯CPU的個(gè)數(shù)一致，但這并不意味著Scheduler和固定的邏輯CPU相綁定，而是Scheduler可以在任何CPU上運(yùn)行，只有您設(shè)置了Affinity Mask之后，Scheduler才會(huì)被固定在某個(gè)CPU上。這樣的一個(gè)好處是，當(dāng)一個(gè)Scheduler非常繁忙時(shí)，可能不會(huì)導(dǎo)致只有一個(gè)物理CPU繁忙，因?yàn)镾cheduler會(huì)在多個(gè)CPU之間移動(dòng)，從而使得CPU的使用傾向于平均。

這意味著對(duì)于一個(gè)比較長(zhǎng)的查詢，可以前半部分在CPU0上執(zhí)行，而后半部分在CPU1上執(zhí)行。

另外，在每一個(gè)Scheduler上，同一時(shí)間只能有一個(gè)Worker運(yùn)行，所有的資源都就緒但沒有拿到Scheduler，那么這個(gè)Worker就處于Runnable狀態(tài)。下面讓我們來看一看Worker。

Worker

每一個(gè)Worker可以看做是對(duì)應(yīng)一個(gè)線程（或纖程），Scheduler不會(huì)直接調(diào)度線程，而是調(diào)度Worker。Worker會(huì)隨著負(fù)載的增加而增加，換句話說，Worker是按需增加，直到增加到最大數(shù)字。在SQL Server中，默認(rèn)的Worker最大數(shù)是由SQL Server進(jìn)行管理的。根據(jù)32位還是64位，以及CPU的數(shù)量來設(shè)置最大Worker,具體的計(jì)算公式，您可以參閱BOL：http://msdn.microsoft.com/zh-cn/library/ms187024(v=sql.105).aspx。當(dāng)然您也可以設(shè)置最大Worker數(shù)量，如圖4所示。

圖4.設(shè)置最大Worker數(shù)量

如果是自動(dòng)配置，那么SQL Server的最大工作線程數(shù)量可以在sys.dm_os_sys_info中看到，如圖5所示。

圖5.查看自動(dòng)配置的最大Worker數(shù)量

一般來說，這個(gè)值您都無需進(jìn)行設(shè)置，但也有一些情況，需要設(shè)置這個(gè)值。那就是Worker線程用盡，此時(shí)除了DAC之外，您甚至無法連入SQL Server。

Worker實(shí)際上會(huì)對(duì)應(yīng)Windows上的一個(gè)線程，并與某個(gè)特定Scheduler綁定，每一個(gè)Worker只要開始執(zhí)行Task,除非Task完成，否則Worker永遠(yuǎn)不會(huì)放棄這個(gè)Task,如果一個(gè)Task在運(yùn)行過程由于鎖、IO等陷入等待，那么實(shí)際上Worker就會(huì)陷入等待。

此外，同一個(gè)連接內(nèi)的多個(gè)Batch之間傾向于使用同一個(gè)Worker,比如第一個(gè)Batch使用了Worker 100,那么第二個(gè)Batch也同樣傾向于是用Worker 100，但這并不絕對(duì)。

正在運(yùn)行的任務(wù)所是用的Worker，我們可以通過DMV sys.dm_exec_requests查看正在運(yùn)行的任務(wù)，其中的Task_Address列可以看到正在運(yùn)行的Task,再通過sys.dm_os_tasks的Worker_Address來查看對(duì)應(yīng)的Worker。

SQL Server會(huì)為每一個(gè)Worker保留大約2M左右的內(nèi)存，對(duì)于每一個(gè)Scheduler上所能有的Worker數(shù)量是服務(wù)器的最大Worker數(shù)量/在線的Scheduler,每一個(gè)Scheduler所綁定的Worker會(huì)形成Worker池，這意味著每一個(gè)Scheduler需要Worker時(shí)，首先在Worker池中中查找空閑的Worker，如果沒有空閑的Worker時(shí)，才會(huì)創(chuàng)建新的Worker。這個(gè)行為會(huì)和連接池類似。

那么當(dāng)一個(gè)Scheduler空閑超過15分鐘，或是Windows面臨內(nèi)存壓力時(shí)。SQL Server就會(huì)嘗試Trim這個(gè)Worker池來釋放被Worker所占用的內(nèi)存。

Task

Task是Worker上運(yùn)行的最小任務(wù)單元。只能拿到Worker的Task才能夠運(yùn)行。我們可以看下面一個(gè)簡(jiǎn)單的例子，如代碼1所示。

SELECT @@VERSION 
go

SELECT @@SPID 
go

代碼1.一個(gè)連接上的兩個(gè)Batch

代碼1中的兩個(gè)Batch屬于一個(gè)連接，每一個(gè)Batch中都是一個(gè)簡(jiǎn)單的Task,如我們前面所說，這兩個(gè)Task更傾向于復(fù)用同一個(gè)Worker，因?yàn)樗麄儗儆谕粋€(gè)連接。但也有可能，這兩個(gè)Task使用了不同的Worker,甚至是不同的Scheduler。

除了用戶所用的Task之外，還有一些永久的系統(tǒng)Task，這類Task會(huì)永遠(yuǎn)占據(jù)Worker,這些Task包括死鎖檢測(cè)、Lazy Writer等。

Task在Scheduler上的平均分配

新的Task還會(huì)嘗試在Scheduler之間平均分配，可以通過sys.dm_os_schedulers來看到一個(gè)load_factor列，這列的值就是用于供Task向Scheduler進(jìn)行分配時(shí)，用來參考。

每次一個(gè)新的Task進(jìn)入Node時(shí)，會(huì)選擇負(fù)載最少的的Scheduler。但是，如果每次都來做一次選擇，那么就會(huì)在Task入隊(duì)時(shí)造成瓶頸(這個(gè)瓶頸類似于TempDB SGAM頁爭(zhēng)搶)。因此SQL OS對(duì)于每一個(gè)連接，都會(huì)記住上次運(yùn)行的Scheduler ID，在新的Task進(jìn)入時(shí)作為提示(Hint)。但如果一個(gè)Scheduler的負(fù)載大于所有Scheduler平均值的20%，則會(huì)忽略這個(gè)提示。負(fù)載可以通過上面提到的load_factor列來看，對(duì)于某個(gè)Task運(yùn)行的時(shí)間比較長(zhǎng)，則很有可能造成Scheduler上Task分配的不均勻。

Worker的Yield

由于SQL Server是非搶占式調(diào)度，那么就不能為了完成某個(gè)Task，讓W(xué)orker占據(jù)Scheduler一直運(yùn)行。如果是這樣，那么處于Runnable的Worker將會(huì)饑餓，這不利于大量并發(fā)，也違背了SQL OS調(diào)度的初衷。

因此，在合適的時(shí)間點(diǎn)讓出Scheduler就是關(guān)鍵。Worker讓出CPU使得其它Worker可以運(yùn)行的過程稱之為yield。yield大體可分為兩種，一種是所謂的“natural yield”,這種方式是Worker在運(yùn)行過程中被鎖或是某些資源阻塞，此時(shí)，該Worker就會(huì)讓出Scheduler來讓其它Worker運(yùn)行。另外一種情況是Worker沒有遇到阻塞，但在時(shí)間片到了之后，主動(dòng)讓出Scheduler,這就是所謂的“voluntarily yield”，這也就是SOS_SCHEDULER_YIELD等待類型的由來，一個(gè)Worker由RUNNING狀態(tài)轉(zhuǎn)到WAITING狀態(tài)的過程被稱之為switching。SQL OS的一個(gè)基本思想就是，要多進(jìn)行switching，來保證高并發(fā)。下面我們來看幾種常見的yield場(chǎng)景:

基于時(shí)間片的voluntarily yield大概使得Worker每4秒yield一次。這個(gè)值可以通過sys.dm_os_schedulers的quantum_length_us列看到。

每64K結(jié)果集排序，就做一次yield。

語句complie,會(huì)做yield。

讀取數(shù)據(jù)頁時(shí)

batch中每一句話做完，就會(huì)做一次yield。

如果客戶端不能及時(shí)取走數(shù)據(jù)，worker也會(huì)做yield。

SQL Server OS中的搶占式任務(wù)調(diào)度

對(duì)于一些代碼來說，SQL Server會(huì)存在一些搶占式代碼。如果您在等待類型中看到“PREEMPTIVE_*”類型的等待，說明這里面的代碼正在運(yùn)行在搶占式任務(wù)調(diào)度模式。這類任務(wù)包括擴(kuò)展存儲(chǔ)過程、調(diào)用Windows API、日志增長(zhǎng)（日志填0）。我們知道，合作式的任務(wù)調(diào)度需要任務(wù)本身Yield，但這類代碼在SQL Server 之外，如果讓他們運(yùn)行在合作式任務(wù)調(diào)度這個(gè)SandBox之內(nèi)，這類代碼如果不yield，則會(huì)永遠(yuǎn)占用Scheduler。這是非常危險(xiǎn)的。

因此，在進(jìn)入搶占式模式之前，首先需要將Scheduler的控制權(quán)交給在Runable隊(duì)列中的下一個(gè)Worker。此時(shí)，搶占式模式運(yùn)行的代碼不再由SQL OS控制，轉(zhuǎn)而由Windows任務(wù)調(diào)度系統(tǒng)控制。因此一個(gè)Task的生命周期如果再加上轉(zhuǎn)到搶占式任務(wù)調(diào)度模式，則會(huì)如圖6所示。

圖6.一個(gè)Task完整的生命周期

每一個(gè)Scheduler的任務(wù)調(diào)度

對(duì)于每一個(gè)Scheduler的調(diào)度，一個(gè)簡(jiǎn)單的模型如圖7所示。

圖7.一個(gè)Scheduler的調(diào)度周期模型

小結(jié)

SQL Server OS在Windows之上抽象出一套非搶占式的任務(wù)調(diào)度機(jī)制，從而減少了Context Switch。同時(shí)，又有一套線程自己的yield機(jī)制，相比Windows隨機(jī)搶占數(shù)據(jù)庫之內(nèi)的線程而言，讓線程自己來yield則會(huì)大量減少Context Switch,從而提升了并發(fā)性。