SQL Server Nuts 堅果的堅持 Day2 (SQLOS)

2022-10-01

SQL Server 堅果的堅持學習筆記系列，不中斷的連續學習，深入 SQL Server 核心知識 🥜

說明

Execution Model and Wait Statistics

Best Practice Checklist

掌握資料庫伺服器的 Wait statistics

分析 signal waits 與 resource waits 的百分比關係

學習設定 Resource Governor configuration

細說 SQLOS

SQL Server 資料傳輸是透過 TDS Protocol，在傳輸層級下包含 Query Processor。Query Processor 可以在分為負責執行計畫產生、成本計算與資料表統計資訊的 Query Optimization 以及負責平行化 (Parallelism) 以及記憶體分配的 Query Execution。

在往底層深入則包含 Storage Engine 及 In-Memory OLTP Engine，最後則是負責資源管理、偵測死結 (Deadlock Dectection) 與排程 (Scheduling) 的 SQLOS。

TDS Protocl

Tabular Data Stream (TDS)，負責在 SQL Server 與應用程式端進行溝通的協定。 TDS 在跨伺服器的的使用是可以基於網路層的傳輸協定 TCP/IP 及 Named Pipe，如果在同一台伺服器則可以用 Shared Memory。

Query Processor

負責查詢 (Query) 的優化 (Optimization) 以及執行 (Execution)，分析、優化與管理查詢所需的執行計畫 (Plan)。

Storage Engine

負責 SQL Server 與 Disk 的資料存取與管理，並且負責處理交易紀錄 (Transaction Logs) 與鎖定 (Locking)。

SQLOS

與作業系統分工，負責管理與調度資料庫的元件 (Components)，進行資源管理、偵測死結 (Deadlock Dectection) 與排程 (Scheduling) 等工作，各資料庫元件 (Components) 不會直接向作業系統取得資源，而是經由 SQLOS 來取得資源 (例如 Memory)。在 SQLOS 的角度，包含下列三者：

Scheduler (CPUs)
Worker (Threads)
Task (Thread Hnadle)

UMS

User Mode Scheduler 是 SQL Server 所採用的 Scheduler 演算法，屬於 cooperative scheduling 而非 preemptive scheduling。

在啟用 SQL Server 時，SQLOS 會啟用一組的 schedulers 藉此代表來自各 CPU 的資源以進行各種工作的處理。從簡化的角度，可以直接將 schedulers 視作 CPUs。

Schedulers 負責管理 Work Threads (簡稱為 Workers)，在 SQL Server 的伺服器組態設定，可以設定 Max Worker, maximum number of workers，預設值為 0 表示 SQL Server 是根據 Schedulers 的數量來決定 Worker Trheads 最大數量。

每當有 Task 被進行，會將 Task 分配給閒置的 Worker 負責，當沒有閒置的 Worker 時，Schedulers 會建立新的 Worker 因應需求。此外當 Worker 閒置 15 分鐘或者在記憶體使用壓力的情況下，會將 Worker 消滅。每個 Worker 在 32位元架構下，使用 512 KB，在 64 位元架構下則使用 2 MB。

可以將 Workers 視為作業系統 Threads，而將 Tasks 視為工作的單位，即 Threads Handle。

Task Steps

PENDING: Task 被創造後等待 Worker 以執行
RUNNING: Task 正在被 Scheduler 執行
RUNNABLE: 等待 Scheduler 以執行
SUSPENDED: 等待外部事件或資源
SPINLOOP: Task is processing 正在處理 Spinlock
DONE: Task 已完成

SELECT * FROM sys.dm_os_spinlock_stats

提升效能之一，減少 RUNNING time，藉由提升硬體等級，使用最好的 CPUs，減少 Tasks 數。
提升效能之二，減少 RUNNABLE time，增加 CPUs 資源，減少系統負擔。
提升效能之三，減少 SUSPENDED time，避免讓 Task 處於等待資源的狀態，是提升效能最有效的方式。

根據 Korotkevitch 所說明的 Query 方式，可以藉由 sys.dm_os_wait_stats 觀察 SUSPENDED time 及發生的各種 wait type。

;WITH Waits
AS
(
    SELECT 
    wait_type, wait_time_ms, waiting_tasks_count,signal_wait_time_ms
    ,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
    ,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS Pct
    ,100. * SUM(wait_time_ms) OVER(ORDER BY wait_time_ms DESC) /
        NULLIF(SUM(wait_time_ms) OVER(), 0) AS RunningPct
    ,ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE 
    wait_type NOT IN /* 移除不需要觀察的系統等待資訊 */
    (N'BROKER_EVENTHANDLER',N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP'
    ,N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER',N'CHECKPOINT_QUEUE',N'CHKPT'
    ,N'CLR_SEMAPHORE',N'CLR_AUTO_EVENT',N'CLR_MANUAL_EVENT'
    ,N'DBMIRROR_DBM_EVENT',N'DBMIRROR_EVENTS_QUEUE',N'DBMIRROR_WORKER_QUEUE'
    ,N'DBMIRRORING_CMD',N'DIRTY_PAGE_POLL',N'DISPATCHER_QUEUE_SEMAPHORE'
    ,N'EXECSYNC',N'FSAGENT',N'FT_IFTS_SCHEDULER_IDLE_WAIT',N'FT_IFTSHC_MUTEX'
    ,N'HADR_CLUSAPI_CALL',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
    ,N'HADR_LOGCAPTURE_WAIT',N'HADR_NOTIFICATION_DEQUEUE'
    ,N'HADR_TIMER_TASK',N'HADR_WORK_QUEUE',N'KSOURCE_WAKEUP',N'LAZYWRITER_SLEEP'
    ,N'LOGMGR_QUEUE',N'ONDEMAND_TASK_QUEUE'
    ,N'PARALLEL_REDO_WORKER_WAIT_WORK',N'PARALLEL_REDO_DRAIN_WORKER'
    ,N'PARALLEL_REDO_LOG_CACHE',N'PARALLEL_REDO_TRAN_LIST'
    ,N'PARALLEL_REDO_WORKER_SYNC',N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS'
    ,N'PREEMPTIVE_OS_LIBRARYOPS',N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_PIPEOPS'
    ,N'PREEMPTIVE_OS_GENERICOPS',N'PREEMPTIVE_OS_VERIFYTRUST'
    ,N'PREEMPTIVE_OS_FILEOPS',N'PREEMPTIVE_OS_DEVICEOPS'
    ,N'PREEMPTIVE_OS_QUERYREGISTRY',N'PREEMPTIVE_XE_CALLBACKEXECUTE'
    ,N'PREEMPTIVE_XE_DISPATCHER',N'PREEMPTIVE_XE_GETTARGETSTATE'
    ,N'PREEMPTIVE_XE_SESSIONCOMMIT',N'PREEMPTIVE_XE_TARGETINIT'
    ,N'PREEMPTIVE_XE_TARGETFINALIZE',N'PWAIT_ALL_COMPONENTS_INITIALIZED'
    ,N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',N'PWAIT_EXTENSIBILITY_CLEANUP_TASK'
    ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',N'QDS_ASYNC_QUEUE'
    ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
    ,N'REQUEST_FOR_DEADLOCK_SEARCH',N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK'
    ,N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP',N'SLEEP_DCOMSTARTUP'
    ,N'SLEEP_MASTERDBREADY',N'SLEEP_MASTERMDREADY',N'SLEEP_MASTERUPGRADED'
    ,N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK',N'SLEEP_TASK'
    ,N'SLEEP_TEMPDBSTARTUP',N'SNI_HTTP_ACCEPT',N'SOS_WORK_DISPATCHER'
    ,N'SP_SERVER_DIAGNOSTICS_SLEEP',N'SQLTRACE_BUFFER_FLUSH'
    ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES'
    ,N'STARTUP_DEPENDENCY_MANAGER',N'WAIT_FOR_RESULTS'
    ,N'WAITFOR',N'WAITFOR_TASKSHUTDOWN',N'WAIT_XTP_HOST_WAIT'
    ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',N'WAIT_XTP_CKPT_CLOSE',N'WAIT_XTP_RECOVERY'
    ,N'XE_BUFFERMGR_ALLPROCESSED_EVENT',N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT'
    ,N'XE_LIVE_TARGET_TVF',N'XE_TIMER_EVENT')
)
SELECT
    w1.wait_type AS [Wait Type]
    ,w1.waiting_tasks_count AS [Wait Count]
    ,CONVERT(DECIMAL(12,3), w1.wait_time_ms / 1000.0) AS [Wait Time]
    ,CONVERT(DECIMAL(12,1), w1.wait_time_ms / w1.waiting_tasks_count) AS [Avg Wait Time]
    ,CONVERT(DECIMAL(12,3), w1.signal_wait_time_ms / 1000.0) AS [Signal Wait Time]
    ,CONVERT(DECIMAL(12,1), w1.signal_wait_time_ms / w1.waiting_tasks_count) AS [Avg Signal Wait Time]
    ,CONVERT(DECIMAL(12,3), w1.resource_wait_time_ms / 1000.0) AS [Resource Wait Time]
    ,CONVERT(DECIMAL(12,1), w1.resource_wait_time_ms / w1.waiting_tasks_count) AS [Avg Resource Wait Time]
    ,CONVERT(DECIMAL(6,3), w1.Pct) AS [Percent]
    ,CONVERT(DECIMAL(6,3), w1.RunningPct) AS [Running Percent]
FROM
    Waits w1
WHERE
    w1.RunningPct <= 99 OR w1.RowNum = 1
ORDER BY
    w1. RunningPct  
OPTION (RECOMPILE, MAXDOP 1);

Useful DMVs For TroubleShooting

sys.dm_os_wait_stats

藉由觀察 signal wait time (RUNNABLE wait) 與 resource wait time (SUSPEND wait) 的比率，一般而言 signal wait time 會屆於 10% 至 15%。

SELECT
	CONVERT(DECIMAL(7,4), 100.0 * SUM (signal_wait_time_ms) 
		/ SUM(wait_time_ms)) AS [% Signal waits]
	,CONVERT (DECIMAL(7,4), 100.0 * sum(wait_time_ms - signal_wait_time_ms) 
		/ SUM(wait_time_ms)) AS [% Resource waits]
FROM sys.dm_os_wait_stats

sys.dm_exec_session_wait_stats

從 Session 的層級，進行各種等待的統計，當 Session 被結束，統計就會歸零。欄位與 sys.dm_os_wait_stats 相似，同樣可以觀察 signal wait time 與 resource wait time 的比率。

sys.dm_os_waiting_tasks

列出所有正在 SUSPENDED 的 Tasks，可以用於處理資料庫伺服器效能不佳以及確認查詢的 Blocking 情形。

sys.dm_exec_request

列出所有執行中的 Request，可以藉由欄位 sql_handle 以及 plan_handle 取得 Request 的執行內容。

sys.dm_os_schedulers

觀察 SQL Server Schedulers (CPUs) 的資訊，同時可以藉由 parent_node_id 來觀察伺服器的 NUMA 節點數。可以觀察 current_tasks_count 所代表的 RUNNING 以及 runnable_tasks_count 所代表的 RUNNABLE Task 數。

主動清除統計資料，而不需要重新啟動 SQL Server Service 的方式：