765
SqlServer基礎--SQLOS 的任務調度（轉）
【介紹】
SQL Server 通過WORKER, SCHEDULER, TASK等來對任務進行調度和處理。了解這些概念，對於了解SQL Server 內部是如何工作，是非常有幫助的。
通常來講，SCHEDULER個數是跟CPU個數相匹配的 。除了幾個係統的SCHEDULER以外，每一個SCHEDULER都映射到一個CPU，如下麵的查詢結果所示，我們有四個CPU，也就有相應四個SCHEDULER。
SELECT cpu_count,scheduler_count,scheduler_total_count,max_workers_count FROM sys.dm_os_sys_info
而WORKER (又稱為WORKER THREAD), 則是工作線程。在一台服務器上，我們可以有多個工作線程。因為每一個工作線程要耗費資源，所以，SQL Server有一個最大工作線程數。一個TASK進來，係統會給它分配一個工作線程進行處理。但是當所有的工作線程都在忙，而且已經達到了最大工作線程數，SQL Server就要等待，直到有一個忙的工作線程被釋放。最大工作線程數可以通過下麵的查詢得到。SQL SERVER並不是一開始就把這些所有的工作線程都創建，而是依據需要而創建。
SELECT cpu_count,scheduler_count,scheduler_total_count,max_workers_count FROM sys.dm_os_sys_info
TASK是由BATCH而來。我們知道，一個連接，可以包含多個BATCH，而每個BATCH則可以分解成多個TASK。如下麵某一個連接要做的事情。這個連接要做的有兩個BATCH，而每個BATCH，如SELECT * FROM TABLE_B，因為可以支持並行化查詢，所以可能會被分解成多個TASK。具體BATCH怎麼分解成TASK，以及分解成多少個，則是由SQL Server內部決定的。
INSERT INTO TABLE_B VALUES (‘aaa’)
GO
SELECT * FROM TABLE_B
GO
【關係】
我們初步了解了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 Test4;
GO
USE Test4;
GO
CREATE TABLE Test
(ID INT,
name NVARCHAR(50)
);
INSERT INTO Test
VALUES(1,'aaa');
步驟二：
打開一個查詢窗口，執行下麵的語句，注意，我們這裏並沒有commit transaction.
USE Test4
BEGIN TRAN;
UPDATE Test SET name = 'bbb'
WHERE [ID] = 1;
步驟三：
打開另外一個窗口，執行下麵的語句，我們會看到，下麵的查詢會一直在執行，因為我們前麵的一個transaction並沒有關閉。從查詢窗口，我們可以看到，下麵語句執行的SPID為58
USE Test4
SELECT * FROM Test
步驟四：查看連接。
從下麵的查詢來看，我們的連接對應的SPID是58，被block住了。
SELECT * FROM sys.sysprocesses where spid=56
步驟五：查看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
SELECT * FROM sys.dm_os_tasks where session_id=56
步驟八：查看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狀態。
SELECT * FROM sys.dm_os_schedulers
【應用】
我們了解了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。
PS C:\WINDOWS\system32> sqlcmd.exe /?
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.
usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-K application intent]
[-M multisubnet failover]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
sqlcmd.exe -U sa -P 123456 -S localhost -d Test4 -q " SELECT * FROM TEST "
【總結】
SQL Server的任務調度使得SQL SERVER能夠以最快方式處理用戶發過來的請求。了解SQL SERVER的任務調度過程，對於我們調整係統性能是非常有幫助的。如適當增加MAX WORKER THREAD，調整MAXDOP，去除BLOCKING等等，了解這些概念，會使得我們的調整更有目的性。
最後更新：2017-11-06 09:33:42