SQL Server Architecture: SQLOS, Buffer Pool, and Plan Cache Explained

Every performance problem eventually traces back to one of three resources: memory, CPU scheduling, or I/O. Understanding how SQL Server manages all three internally is the foundation this entire course builds on — every later module’s diagnostic technique is really a way of inspecting one of these three subsystems more closely.

SQLOS: The Layer Beneath T-SQL

SQLOS is SQL Server’s own thin operating-system layer, sitting between the Windows/Linux OS and the relational engine. It manages scheduling (via non-preemptive “SQLOS schedulers” mapped roughly to CPU cores), memory allocation, and synchronization — SQL Server largely manages its own thread scheduling rather than leaving it entirely to the OS, which is why a CPU-bound SQL Server workload behaves differently from a typical application.

-- See the schedulers directly — one row per logical CPU SQL Server is using
SELECT scheduler_id, cpu_id, status, is_online, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

A consistently high runnable_tasks_count across schedulers is an early, concrete sign of genuine CPU pressure — more tasks are ready to run than there are schedulers to run them, so they queue.

The Buffer Pool: Memory’s Biggest Consumer

Checking Buffer Pool Pressure

SELECT COUNT(*) * 8 / 1024 AS cached_data_mb
FROM sys.dm_os_buffer_descriptors;

SELECT total_physical_memory_kb / 1024 AS total_ram_mb,
       available_physical_memory_kb / 1024 AS available_ram_mb
FROM sys.dm_os_sys_memory;

Checking Plan Cache Health

SELECT objtype, COUNT(*) AS plan_count, SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

Why This Matters for Everything Ahead

When a query is slow, the real question is always: is it waiting on disk I/O because the data wasn’t in the buffer pool? Is it recompiling because plan cache pressure evicted it? Or is CPU scheduling itself the constraint? Every later module — indexing, query optimization, monitoring — is really about managing these same three resources more efficiently.

SQL Server Page Structure and Transaction Log Architecture Explained

Lesson 1 covered how SQL Server manages memory and CPU. This lesson covers the physical layer underneath both: how data is actually laid out on disk, and how every single change — no matter how it got there — is guaranteed durable before it’s ever considered committed.

Pages and Extents

This 8KB figure isn’t arbitrary trivia — it’s the exact unit Chapter 4’s STATISTICS IO “logical reads” count is measured in. When that number reports 500 logical reads, it means 500 8KB pages were touched — tying this architecture lesson directly to a diagnostic number you’ll read constantly for the rest of this course.

The Transaction Log: Write-Ahead Logging

-- Check log space usage — a classic "why is my log huge" starting point
DBCC SQLPERF(LOGSPACE);

-- Check log file growth/autogrowth settings
SELECT name, size/128 AS size_mb, growth, is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG';

SQL Server uses Write-Ahead Logging (WAL): a change is written to the transaction log before the data page itself is modified on disk, and a transaction is only considered committed once its log record is durably written. This is literally how Durability (the “D” in ACID from the Developers & DBAs course) is implemented — not a separate feature, but the mechanism underneath it.

Why Log Architecture Explains Real Symptoms

A transaction log that won’t shrink, a database stuck in “log full” errors during a bulk load, a mysteriously slow bulk delete — these all trace back to how logging works: every logged operation, including large deletes, must be written to the log before it’s considered durable, row by row. A DELETE affecting a million rows generates roughly a million log records, regardless of how fast the actual data-page changes would otherwise be.

Module 1 Exercises: SQL Server Architecture Labs

5 guided labs, 3 challenge scenarios, and 2 break-it labs — applying the Evidence-First workflow to real architecture questions.

Guided Labs (step-by-step)

SELECT COUNT(*) * 8 / 1024 AS cached_data_mb FROM sys.dm_os_buffer_descriptors;
SELECT total_physical_memory_kb/1024 AS total_ram_mb FROM sys.dm_os_sys_memory;

SELECT TOP 5 objtype, size_in_bytes/1024 AS size_kb, usecounts
FROM sys.dm_exec_cached_plans ORDER BY size_in_bytes DESC;

DBCC SQLPERF(LOGSPACE);

CREATE TABLE dbo.PageDemo (id INT IDENTITY PRIMARY KEY, filler CHAR(500));
INSERT INTO dbo.PageDemo (filler)
SELECT TOP 10000 REPLICATE('x',500) FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SELECT page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PageDemo'), NULL, NULL, 'DETAILED');

Challenge Scenarios (diagnose independently)

Break-It Labs

Module 1 Quiz: SQL Server Architecture Fundamentals

10 questions, mixing conceptual theory with diagnostic interpretation — including a couple of deliberate misconception traps. Try each one yourself before revealing the answer.