Engine components and SQLOS — schedulers, workers and waits — SQL Server Internals — How the Database Engine Really Works

1. Engine components and SQLOS — schedulers, workers and waits

Intermediate★ Must knowLesson 1 of 11

Component	Responsibility
Protocol layer (SNI)	Network connections, TDS protocol, shared memory/TCP/named pipes
Command parser & algebrizer	Syntax and name/type resolution
Query optimizer	Builds the execution plan from statistics and costs
Query executor	Runs plan operators, requests data from access methods
Access methods	Code that scans heaps, seeks/scans B-trees, inserts/updates rows
Buffer manager	Manages the buffer pool (cached data pages in RAM)
Transaction manager / log manager	Transactions, write-ahead logging, recovery
Lock manager	Locks for concurrency control; deadlock detection (every 5 s by default, faster when deadlocks are found)
SQLOS	SQL Server's "OS layer": cooperative scheduling, memory management, I/O, waits

Scheduling: SQLOS creates one scheduler per logical CPU. Each task (a request or part of a parallel query) runs on a worker (thread). Workers yield cooperatively; when a task must wait (for a page read, a lock, a log flush, memory, network), it goes to a waiting list with a wait type. When the resource is ready it moves to the runnable queue.

text

RUNNING ──needs resource──▶ SUSPENDED (wait type, e.g. PAGEIOLATCH_SH) ──resource ready──▶ RUNNABLE (waiting for CPU = signal wait) ──▶ RUNNING

Wait statistics tell you what SQL Server spends time waiting on:

Wait type	Means	Typical cause
`PAGEIOLATCH_*`	Waiting to read data pages from disk	Missing indexes (big scans), not enough memory, slow storage
`LCK_M_*`	Waiting for a lock	Blocking — long transactions, missing indexes
`WRITELOG`	Waiting for log flush on commit	Slow log disk, many tiny transactions
`CXPACKET` / `CXCONSUMER`	Parallelism coordination	Large parallel queries; tune MAXDOP/cost threshold
`SOS_SCHEDULER_YIELD`	CPU pressure	CPU-heavy queries
`RESOURCE_SEMAPHORE`	Waiting for a memory grant	Big sorts/hashes, bad estimates
`ASYNC_NETWORK_IO`	Client isn't consuming results fast enough	App reads rows slowly or fetches too much

sql

SELECT TOP 10 wait_type, wait_time_ms / 1000.0 AS wait_s, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%' AND wait_type NOT IN ('BROKER_TASK_STOP','XE_TIMER_EVENT','REQUEST_FOR_DEADLOCK_SEARCH','LAZYWRITER_SLEEP','CHECKPOINT_QUEUE')
ORDER BY wait_time_ms DESC;