Parsing, Algebrizer, Optimization, Costing, Memo Search, and Plan Caching

Learning outcomes

A ServiceHub query can be perfectly valid SQL and still arrive with a plan that is surprising for today's parameters. Before blaming an index, you need a mental model of compilation: SQL Server parses text, binds names and types, simplifies the relational expression, searches alternative physical strategies, costs those alternatives, and stores reusable compiled plans when appropriate. The optimizer's job is not to time every possible plan. It predicts relative resource work from estimates, chooses a sufficiently good alternative under a finite search budget, and hands the execution engine a physical plan.

1. Compilation is a pipeline, not a stopwatch competition

The parser checks grammar and produces an internal representation. The algebrizer—often described as the binder—resolves database/schema/object/column names, derives types, checks references, and converts syntax into a relational expression the optimizer can reason about. Simplification can remove redundant expressions or transform logically equivalent constructs. The cost-based optimizer then considers physical operators and access paths.

SQL Server optimizer literature commonly describes an internal memo structure used to represent groups of logically equivalent alternatives during search. Treat that as an implementation mental model, not a supported catalog you should query or a structure applications may depend on. What is supported and observable is the resulting Showplan, compilation/cache metadata, optimizer counters, statistics, and runtime evidence.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab10') IS NULL EXEC(N'CREATE SCHEMA lab10 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab10.WorkOrderFact;CREATE TABLE lab10.WorkOrderFact(  work_order_id bigint IDENTITY(1,1) NOT NULL,  customer_id int NOT NULL,  region_code char(3) NOT NULL,  status varchar(12) NOT NULL,  priority tinyint NOT NULL,  opened_at datetime2(0) NOT NULL,  closed_at datetime2(0) NULL,  amount decimal(12,2) NOT NULL,  notes varchar(200) NULL,  CONSTRAINT PK_lab10_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id));;WITH n AS(  SELECT TOP (30000)         ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n  FROM sys.all_objects AS a  CROSS JOIN sys.all_objects AS b)INSERT lab10.WorkOrderFact(customer_id,region_code,status,priority,opened_at,closed_at,amount,notes)SELECT CASE WHEN n <= 18000 THEN 1 ELSE 2 + n % 1999 END,       CASE WHEN n <= 21000 THEN 'N01' WHEN n <= 27000 THEN 'W02' ELSE 'E03' END,       CASE WHEN n % 20 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,       CASE WHEN n <= 21000 THEN 1 ELSE 5 END,       DATEADD(minute,n,'2026-01-01T00:00:00'),       CASE WHEN n % 5=0 THEN DATEADD(minute,n+90,'2026-01-01T00:00:00') END,       CAST(20 + (n % 5000) / 10.0 AS decimal(12,2)),       CASE WHEN n % 37=0 THEN REPLICATE('x',160) ENDFROM n;CREATE INDEX IX_lab10_RegionStatusOpenedON lab10.WorkOrderFact(region_code,status,opened_at)INCLUDE(customer_id,priority,amount);CREATE INDEX IX_lab10_CustomerON lab10.WorkOrderFact(customer_id)INCLUDE(region_code,status,opened_at,amount);GO

The skew is deliberate: customer 1 and region N01 dominate. Later lessons use that skew to separate a normal cached-plan mechanism from a genuinely parameter-sensitive workload.

2. Cost is an optimizer unit, not elapsed time

Showplan properties such as Estimated Subtree Cost are produced from the optimizer's cost model. They compare candidate plans under assumptions about cardinality, I/O, CPU, row width, and operators. A plan showing “80% cost” on one branch does not mean that branch consumed 80% of actual elapsed time. Actual runtime can diverge because of cache state, concurrency, storage latency, spills, waits, stale estimates, parallel scheduling, or parameter values.

SET SHOWPLAN_XML ON;GOSELECT work_order_id,customer_id,opened_at,amountFROM lab10.WorkOrderFactWHERE region_code='E03'  AND status='ESCALATED'  AND opened_at >= '2026-01-10';GOSET SHOWPLAN_XML OFF;GO

SHOWPLAN_XML prevents execution and returns compile-time plan XML. It must be issued as its own batch and requires SHOWPLAN permission on every referenced database. In SSMS, “Display Estimated Execution Plan” exposes the same kind of compile-time evidence. If this were an UPDATE, no rows would change because the statement never executes.

3. Cache reuse depends on more than similar-looking SQL

Compiled plans can be reused to avoid compilation work, but “same business query” is not necessarily the same cache entry. Query text, parameterization, database context, object/schema changes, relevant SET options, and other cache-key attributes matter. Memory pressure or cache-management events can evict plans. Recompilation can also be triggered by schema/statistics changes or explicit mechanisms such as OPTION (RECOMPILE).

DECLARE @sql nvarchar(max)=N'SELECT COUNT_BIG(*) AS rows_foundFROM lab10.WorkOrderFactWHERE region_code=@region;';EXEC sys.sp_executesql @sql,N'@region char(3)',@region='N01';EXEC sys.sp_executesql @sql,N'@region char(3)',@region='E03';GOSELECT TOP (20)       qs.execution_count,qs.plan_generation_num,       qs.total_worker_time,qs.total_elapsed_time,       SUBSTRING(st.text,(qs.statement_start_offset/2)+1,         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)          ELSE qs.statement_end_offset END-qs.statement_start_offset)/2)+1) AS statement_text,       qp.query_planFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE st.text LIKE N'%lab10.WorkOrderFact%region_code=@region%'ORDER BY qs.last_execution_time DESC;GO

The DMV values are instance/cache snapshots, not durable history. On SQL Server 2022+ many server-scoped performance DMVs require VIEW SERVER PERFORMANCE STATE; the course lab assumes an administrator-level local account. Query Store, covered more deeply in Chapter 11, is the better persisted source when you need history across cache churn.

4. Wrong approach: clear the whole cache because one plan is bad

A real operator may run DBCC FREEPROCCACHE after seeing one poor execution. That discards useful cached plans instance-wide, can cause a compilation surge, and removes evidence you still need. It also does not fix the reason the plan was poor. The safer sequence is: identify the exact query; compare estimates to runtime; inspect statistics and parameters; reproduce; then use a scoped mechanism such as query recompilation, Query Store forcing/hints, or a query/schema/statistics fix when justified.

SELECT TOP (10) cp.plan_handle,cp.usecounts,cp.size_in_bytes,       pa.attribute,pa.valueFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS paCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS stWHERE st.text LIKE N'%lab10.WorkOrderFact%'  AND pa.attribute IN ('dbid','set_options','user_id','language_id')ORDER BY cp.usecounts DESC,pa.attribute;GO

5. Production judgment

Compilation is valuable work, but excessive compilation can consume CPU; over-aggressive reuse can also be harmful when one plan cannot serve highly different parameter shapes. Monitor compilation rates, cache churn, Query Store regressions, CPU, memory pressure, and estimate/runtime differences together. The lab baseline is SQL Server 2025 CU7 build 17.0.4065.4, compatibility 170, free Developer/Express, single local instance, with SSMS 22.8.2 or supported VS Code MSSQL/sqlcmd as clients. No restart or paid feature is required.

Authoritative references

• Query processing architecture guide — compilation, optimization and execution architecture
• SET SHOWPLAN_XML — compile-time plan without execution
• sys.dm_exec_query_plan — cached-plan XML
• sys.dm_exec_query_stats — transient execution/cache counters
• SQL Server 2025 build versions — CU/build servicing baseline
• SSMS 22 release notes — current client-tool baseline