前些天在處理一個SQL Server LATCH導致的資料庫停止響應問題時，遇到了一些需要SQLOS調度知識解決的問題，正好以前看過一篇官網的文章，在這裡稍作修改貼出來。 原文網址如下： https://blogs.msdn.microsoft.com/apgcdsd/2011/11/23/sql- ...
前些天在處理一個SQL Server LATCH導致的資料庫停止響應問題時，遇到了一些需要SQLOS調度知識解決的問題，正好以前看過一篇官網的文章，在這裡稍作修改貼出來。
原文網址如下：
https://blogs.msdn.microsoft.com/apgcdsd/2011/11/23/sql-server-sqlos/
【介紹】
SQL Server在通過BATCH，TASK，WORKER，SCHEDULER等來對任務進行調度和處理。瞭解這些概念，對於瞭解SQL Server內部是如何工作，是非常有幫助的。
通常來講，SCHEDULER個數是跟CPU個數相匹配的。除了幾個系統的SCHEDULER以外，每一個SCHEDULER都映射到一個CPU，如下麵的查詢結果所示，我們有四個CPU，也就有相應四個USER SCHEDULER，而scheduler_total_count有16個則是因為有8個是系統scheduler，我們一般不必關註系統scheduler。
select cpu_count,scheduler_count,scheduler_total_count from sys.dm_os_sys_info
而WORKER(又稱為WORKER THREAD), 則是工作線程。在一臺伺服器上，我們可以有多個工作線程。因為每一個工作線程要耗費資源，所以，SQL Server有一個最大工作線程數。
TASK是worker的使用者，每個TASK系統會給它分配一個工作線程進行處理，是一對一的關係但並不綁定。如果所有的工作線程都在忙，而且已經達到了最大工作線程數，SQL Server就要等待，直到有一個忙的工作線程被釋放。
最大工作線程數可以通過下麵的查詢得到。SQL SERVER並不是一開始就把這些所有的工作線程都創建，而是依據需要而創建。
select cpu_count,max_workers_count from sys.dm_os_sys_info
一個客戶端connection可能包含一個或多個BATCH，一般SQL Server引擎會為一個BATCH視為一個TASK，但使用並行化查詢的BATCH會被分解成多個TASK。具體BATCH怎麼分解成TASK，以及分解成多少個，則是由SQL Server內部決定的。但是在這裡我們依然可以使用相關DMV探尋一下大致分配情況：
我們使用spid為63的視窗執行一個複雜的查詢，此查詢使用預設並行度運行（由於有8個CPU因此預設MAXDOP=8）。
select * from sys.dm_os_tasks where session_id=63 order by 7
結果如下：
(33 行受影響)
task_address task_state context_switches_count pending_io_count pending_io_byte_count pending_io_byte_average scheduler_id session_id exec_context_id request_id worker_address host_address parent_task_address
------------------ ---------------------------------- ---------------- --------------------- ----------------------- ------------ ---------- --------------- ----------- ------------------ ------------------ -------------------
0x000000000DB29468 SUSPENDED 4696 510 0 0 0 63 7 0 0x0000000032E02160 0x0000000000000000 0x0000000025E67468
0x000000000DB29088 SUSPENDED 1457 290 0 0 0 63 11 0 0x0000000017FE2160 0x0000000000000000 0x0000000025E67468
0x0000000012358CA8 RUNNING 1937 1945 0 0 0 63 21 0 0x0000000034E84160 0x0000000000000000 0x0000000025E67468
0x0000000012359088 SUSPENDED 2 0 0 0 0 63 32 0 0x000000000685A160 0x0000000000000000 0x0000000025E67468
0x000000000F20D468 SUSPENDED 4489 510 0 0 1 63 4 0 0x000000001FE30160 0x0000000000000000 0x0000000025E67468
0x0000000035F19468 SUSPENDED 1731 290 0 0 1 63 16 0 0x00000002BD8DC160 0x0000000000000000 0x0000000025E67468
0x0000000035F19088 SUSPENDED 2280 1864 0 0 1 63 23 0 0x000000001AA60160 0x0000000000000000 0x0000000025E67468
0x0000000035F18CA8 SUSPENDED 9 0 0 0 1 63 28 0 0x00000002BB60A160 0x0000000000000000 0x0000000025E67468
0x000000002E283468 SUSPENDED 4485 510 0 0 2 63 5 0 0x000000001FE48160 0x0000000000000000 0x0000000025E67468
0x000000001A736108 SUSPENDED 1700 290 0 0 2 63 15 0 0x00000000310C6160 0x0000000000000000 0x0000000025E67468
0x000000001A737468 RUNNING 2256 1865 0 0 2 63 20 0 0x00000000049DC160 0x0000000000000000 0x0000000025E67468
0x000000001A737848 SUSPENDED 5 0 0 0 2 63 30 0 0x0000000018390160 0x0000000000000000 0x0000000025E67468
0x000000001A609088 SUSPENDED 3973 510 0 0 3 63 8 0 0x000000001BEC0160 0x0000000000000000 0x0000000025E67468
0x0000000014A49848 SUSPENDED 1652 290 0 0 3 63 14 0 0x0000000017436160 0x0000000000000000 0x0000000025E67468
0x0000000014A49088 RUNNING 2058 1878 0 0 3 63 18 0 0x0000000025D2C160 0x0000000000000000 0x0000000025E67468
0x000000000FD5C108 SUSPENDED 6 0 0 0 3 63 26 0 0x00000000213DA160 0x0000000000000000 0x0000000025E67468
0x0000000025E67468 SUSPENDED 3 0 0 0 4 63 0 0 0x00000000353A6160 0x0000000000000000 NULL
0x0000000006EC9C28 SUSPENDED 4469 510 0 0 4 63 6 0 0x000000002AF14160 0x0000000000000000 0x0000000025E67468
0x000000001C0708C8 SUSPENDED 1725 290 0 0 4 63 13 0 0x000000002AC74160 0x0000000000000000 0x0000000025E67468
0x000000001C0704E8 RUNNING 2324 1889 0 0 4 63 24 0 0x000000001497A160 0x0000000000000000 0x0000000025E67468
0x0000000012035468 SUSPENDED 5 0 0 0 4 63 29 0 0x00000002B70E6160 0x0000000000000000 0x0000000025E67468
0x00000002BB1144E8 SUSPENDED 4084 511 0 0 5 63 1 0 0x0000000028F4E160 0x0000000000000000 0x0000000025E67468
0x00000002BB115C28 SUSPENDED 1775 290 0 0 5 63 12 0 0x000000000E7B4160 0x0000000000000000 0x0000000025E67468
0x00000002BB115468 RUNNABLE 2256 1830 0 0 5 63 22 0 0x000000000AC4C160 0x0000000000000000 0x0000000025E67468
0x000000000BBA5848 SUSPENDED 5 0 0 0 5 63 27 0 0x000000002ABFC160 0x0000000000000000 0x0000000025E67468
0x00000000263BFC28 SUSPENDED 5031 510 0 0 6 63 2 0 0x000000002E444160 0x0000000000000000 0x0000000025E67468
0x00000002BE5D6108 SUSPENDED 1856 290 0 0 6 63 10 0 0x00000002BF20E160 0x0000000000000000 0x0000000025E67468
0x0000000020446CA8 RUNNING 2275 1936 0 0 6 63 19 0 0x0000000005104160 0x0000000000000000 0x0000000025E67468
0x0000000020446108 SUSPENDED 5 0 0 0 6 63 31 0 0x0000000022F9E160 0x0000000000000000 0x0000000025E67468
0x000000003193B468 SUSPENDED 4276 510 0 0 7 63 3 0 0x000000002B58C160 0x0000000000000000 0x0000000025E67468
0x000000003193A8C8 SUSPENDED 1806 290 0 0 7 63 9 0 0x000000001FCEA160 0x0000000000000000 0x0000000025E67468
0x000000000E2A2CA8 SUSPENDED 2308 2007 0 0 7 63 17 0 0x00000000113AE160 0x0000000000000000 0x0000000025E67468
0x000000000E2A28C8 SUSPENDED 10 0 0 0 7 63 25 0 0x000000002504C160 0x0000000000000000 0x0000000025E67468
從上圖我們可以看到，來自客戶端的一個BACTH由於並行查詢而被分解成了33個TASK，對應33個task_address,和33個worker_address，這說明一個BATCH占用了33個worker threads,這個數目是相當大的。由於本例中USER SCHEDULER的數目是8，因此預設MAXDOP也是8，所以我們看到有編號為0-7的8個scheduler_id，其中scheduler_id為4的CPU被5個task占用，這5個task當中有一個parent_task_address為NULL，說明這個task是整個BATCH的主task。其他7個CPU上都只有4個task。如果觀察時間更長一點我們還會發現，同一個CPU上的4個task只有exec_context_id倒數第二大的task是一直處於running狀態的，其他的全部是處於占用worker thread的suspended狀態。
【關係】
我們初步瞭解了Connection, Batch, Task, Worker, Scheduler, CPU這些概念，那麼，它們之間的關係到底是怎麼樣呢？
如上圖所示，左邊是很多連接，每個連接有一個相應的SPID，只要用戶沒有登出，或者沒有timeout,這個始終是存在的。標準設置下，對於用戶連接數目，是沒有限制的。
在每一個連接里，我們可能會有很多batch，在一個連接里，batch都是按順序的。只有一個batch執行完了，才會執行下麵一個batch。因為有很多連接，所以從SQL Server層面上看，同時會有很多個batch。
SQL Server會做優化，每一個batch，可能會分解成多個task以支持如並行查詢。這樣，在SQL層面上來看，同時會有很多個TASK。
SQL Server上，每一個CPU通常會對應一個Scheduler,有幾個額外的系統的Scheduler，只是用來執行一些系統任務。對用戶來講，我們只需要關心User Scheduler就可以了。如果有4個CPU的話，那麼通常就會有4個User Scheduler。
每個Scheduler上，可以有多個worker對應。Worker是真正的執行單元，Scheduler（對CPU的封裝）是執行的地方。Worker的總數受max worker thread限制。每一個worker在創建的時候，自己需要申請2M記憶體空間。如果max worker thread為1024，並且那些worker全部創建的話，至少需要2G空間。所以太多的worker，會占用很多系統資源。
【跟蹤】
在瞭解Connection, Batch, Task, Worker, Scheduler, CPU之間的關係後，下麵我們用DMV跟蹤一下運作的流程。
步驟一：
執行下麵的腳本，創建一個測試資料庫和測試數據表
CREATE DATABASE TEST
go
use TEST
go
CREATE TABLE TEST(ID int,name nvarchar(50))
INSERT INTO TEST VALUES (1, 'aaa')
步驟二：
打開一個查詢視窗，執行下麵的語句，註意，我們這裡並沒有commit transaction.
begin tran
update TEST set name='bbb' where [ID] = 1
步驟三：
打開另外一個視窗，執行下麵的語句，我們會看到，下麵的查詢會一直在執行，因為我們前面的一個transaction並沒有關閉。從查詢視窗，我們可以看到，下麵語句執行的SPID為58
SELECT * FROM TEST
步驟四：查看連接
從下麵的查詢來看，我們的連接對應的SPID是58，被block住了。
步驟五：查看batch
我們查看SQL Profiler, 看到我們的Batch是SELECT * FROM TEST
步驟六：查看TASK
用下麵的DMV, 我們可以看到，針對SESSION_ID=58的，只有一個task. (地址為0x0064F048), 而針對該TASK的worker地址為: 0x803081A0。同時我們也可以看到該worker運行在Scheduler 0上面。
步驟七：查看WORKER
從下麵的查詢可以知道，這個WORKER已經執行了5291個task了。這個worker相應的Scheduler地址是0x00932080
步驟八：查看SCHEDULER
從下麵的查詢可以得知，Scheduler_address (0x00932080) 相應的CPU_ID是0。在我們的系統上，有4個CPU, 編號分別為0， 1， 2， 3. 但是有7個SCHEDULER, 其中3個是SYSTEM SCHEDULER, 4個是USER SCHEDULER。在每個SCHEDULER上，有相應的WORKER數目。因為WORKER是根據需要而創建的，所以，在每個SCHEDULER上，目前WORKER數目很少。而且其中有些WORKER還處於SLEEPING狀態。
【應用】
我們瞭解了SQL SERVER任務調度的機制，那麼有些問題，就會更加清楚。
設置MAXDOP的作用。MAXDOP=1的話，可以使得一個BATCH只對應一個TASK。如果一個BATCH產生多個TASKS，那麼TASK之間的協調，等待等等，將是很大的開銷。把MAXDOP設小，能同時減少WORKER的使用量。所以，如果我們看到等待類型為CXPACKET的話，那麼我們可以設置MAXDOP，減少並行度。
比較大的SPID。如果我們看到SPID的號碼非常大，如超過1000,那麼通常表明，我們系統有很嚴重的BLOCKING。SQL SERVER不對連接數做限制，但是對於WORKER數，是有限制的。預設情況下，最大個數如下：
|
Number of CPUs |
32bit |
64 bit |
|
<=4 processors |
256 |
512 |
|
8 processors |
288 |
576 |
|
16 processors |
352 |
704 |
|
32 processors |
480 |
960 |
對於很大的SPID編號，通常表明，我們的WORKER數是很高的。這種情況比較危險，如果一個新的連接進來，可能沒有空閑WORKER來處理這個連接。在CLUSTER環境下，ISALIVE檢查會失敗，會導致SQL SERVER做FAILOVER。
NON-YIELDING SCHEDULER錯誤。我們有時候會看到SQL Server會報一個17883錯誤， NON-YIELDING SCHEDULER。這個錯誤指的是，在一個SCHEDULER上，會有多個WORKER，它們以友好的方式，互相占用一會兒SCHEDULER資源。某個WORKER占用SCHEDULER後，執行一段時間，會做YIELD，也就是退讓，把SCHEDULER資源讓出來，讓其他WORKER去使用。如果某一個WORKER出於某種原因，不退讓SCHEDULER資源，導致其他WORKER沒有機會運行，這種現象叫NON-YIELDING SCHEDULER。出現這種情況，SQL SERVER有自動檢測機制，會打一個DUMP出來。我們需要進一步分析DUMP為什麼該WORKER不會YIELD。
WORKER 用完。我們可以做一個小實驗。我們在一臺32位機器上，創建上面提及的測試資料庫，並且，開啟一個同樣的未關閉transaction的update語句。
然後執行下麵的程式。下麵的程式會開啟256個連接到SQL Server, 這256個連接由於前面的transaction未閉合，都處於BLOCKING狀態。
using System;
using System.Diagnostics;
namespace WORKER
{
class Program
{
static void Main(string[] args)
{
for(int i=0; i<256; i++)
{
OpenConnection();
}
}
static void OpenConnection()
{
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = "sqlcmd.exe";
startInfo.Arguments = " -E -S SERVERNAME -d TEST -q \" SELECT * FROM TEST \"";
Process.Start(startInfo);
}
}
}
查詢SELECT * FROM sys.dm_os_tasks這時候我們發現有278個TASK，而查詢sys.dm_os_schedulers 我們發現有兩個CPU, 因此有兩個用戶SCHEDULER, 每個SCHEDULER上，有128個workers. 加起來有256個WORKERS。針對兩個CPU的架構，我們預設最大的WORKER數是256。所以已經到了極限了。
這時候，我們新開啟一個連接，會發現SQL Server連不上，並報如下錯誤:
這是因為WORKER用完的緣故。新的連接無法獲得一個WORKER來做login process。所以導致連接失敗。在群集環境下，如果連接不上SQL Server, ISALIVE檢查會失敗，會引起SQL Server FAILOVER。所有的連接都會被強迫中止，並且SQL Server會在新結點上重新啟動。針對這種情況，我們可以修改提高MAX WORKER THREAD，但是並不能最終解決問題，由於BLOCKING緣故，新的連接會迅速積累，一直把MAX WORKER THREAD用完，所以這時候，我們應該檢查BLOCKING。使得task能及時完成，釋放WORKER。
【總結】
SQL Server的任務調度使得SQL SERVER能夠以最快方式處理用戶發過來的請求。瞭解SQL SERVER的任務調度過程，對於我們調整系統性能是非常有幫助的。如適當增加MAX WORKER THREAD，調整MAXDOP，去除BLOCKING等等，瞭解這些概念，會使得我們的調整更有目的性。
