SQL Server 在 Hyper-V 上的 Virtual Processor 數量設定
在實體運作的 SQL Server 營運環境中，通常不會經常把 CPU 拔起來或多插個幾顆進去。但是當在 Hyper-V 上執行時，方便的 CPU 資源調配讓這件事變得更有可能發生。前陣子一個客戶比對 SQL Server 2005 執行在 Hyper-V (Guest OS : W2k3) 上與實體機器的運作效能時，安裝相同的軟體環境、配置一樣的處理器核心數目與記憶體。卻發現虛擬機上的 SQL Server 執行大量 SQL Jobs 時常常有 Job 出現 "等待工作者執行序" 訊息。從訊息看來是 worker thread 數量不足，但是令人好奇的是在一樣處理器核心數目下，虛擬機使用的 CPU 等級還更好，為何反倒 worker thread 會不夠用呢?
原因在於 SQL Server 在 msdb 裡的系統資料表 syssubsystems 中有一欄 max_worker_threads 會紀錄每一種 Job 的可用 worker thread 上限，而這個值似乎是安裝程式在安裝過程中依據當時偵測到的處理器核心數量決定的。 因為客戶當初安裝 SQL Server 2005 於虛擬機時僅分配一顆 virtual processor ，後來才改分配四顆，因此 syssubsystems 資料表中的值如下:
subsystem |
Max_worker_threads |
TSQL |
20 |
ActiveScripting |
10 |
CmdExec |
10 |
Snapshot |
100 |
LogReader |
25 |
Distribution |
100 |
Merge |
100 |
QueueReader |
100 |
ANALYSISQUERY |
100 |
ANALYSISCOMMAND |
100 |
SSIS |
100 |
可以看到，光 TSQL 同時最多僅能跑 20 個 threads，當然對大量執行 Jobs 的環境不夠用。 解法也很簡單，直接修改系統資料表全部乘以 4 囉...
UPDATE syssubsystems SET max_worker_threads = max_worker_threads * 4