[薦]SQL SERVER SQLOS的任務調度

【介紹】

SQL Server 通過WORKER, SCHEDULER, TASK等來對任務進行調度和處理。瞭解這些概念，對於瞭解SQL Server 內部是如何工作，是非常有協助的。

通常來講，SCHEDULER個數是跟CPU個數相匹配的 。除了幾個系統的SCHEDULER以外，每一個SCHEDULER都映射到一個CPU，如下面的查詢結果所示，我們有四個CPU，也就有相應四個SCHEDULER。

而WORKER (又稱為WORKER THREAD), 則是背景工作執行緒。在一台伺服器上，我們可以有多個背景工作執行緒。因為每一個背景工作執行緒要耗費資源，所以，SQL Server有一個最大背景工作執行緒數。一個TASK進來，系統會給它分配一個背景工作執行緒進行處理。但是當所有的背景工作執行緒都在忙，而且已經達到了最大背景工作執行緒數，SQL Server就要等待，直到有一個忙的背景工作執行緒被釋放。最大背景工作執行緒數可以通過下面的查詢得到。SQL SERVER並不是一開始就把這些所有的背景工作執行緒都建立，而是依據需要而建立。

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 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等等，瞭解這些概念，會使得我們的調整更有目的性。

 

原文：http://blogs.msdn.com/b/apgcdsd/archive/2011/11/24/sql-server-sqlos.aspx