Chapter 25 · Production Capstone: Design, Secure, Tune, Automate, and Recover SQL Server
Load Test and Tune Plans, Query Store, tempdb, Memory, I/O, Columnstore/In-Memory Where Appropriate
Build a representative mixed workload and use Query Store/plans/waits/tempdb/memory/I/O evidence to tune one hypothesis at a time and reject unjustified features.
Learning outcomes
Performance work in the capstone is an experiment, not a collection of tuning rituals. We need a reproducible workload, a baseline, a hypothesis, one controlled change, and evidence that the target workload improved without harming another class. Query Store gives persisted query/runtime/wait evidence; DMVs and plans show current mechanism; application telemetry supplies end-to-end latency. Columnstore and In-Memory OLTP are candidates only if the measured workload matches their strengths.
Generate a deterministic mixed ServiceHub workload without pretending local measurements are universal benchmarks.
Use Query Store, plans, waits, memory-grant/tempdb/I/O evidence to form a bottleneck hypothesis.
Change one index/query/design decision at a time and preserve before/after evidence.
Evaluate columnstore and In-Memory OLTP against concrete workload shapes rather than feature prestige.
Document rejected optimizations and their reasons so future operators do not repeat the same experiment blindly.
ServiceHubCapstone. No production passwords,
certificates, private keys, cloud credentials, or real customer
data are embedded. Run measurements on your own instance and
record CPU count, memory limit, storage, edition, compatibility
170, recovery model and cache/concurrency state. The lesson
deliberately provides no fake “x times faster” numbers.
1. Build representative data before interpreting plans
USE ServiceHubCapstone;GOIF (SELECT COUNT_BIG(*) FROM ops.WorkOrder) < 50000BEGIN ;WITH n AS ( SELECT TOP (50000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn FROM sys.all_objects a CROSS JOIN sys.all_objects b ) INSERT ops.WorkOrder(technician_id,customer_code,status,priority,opened_at,scheduled_at,closed_at,description,region_code) SELECT 1+(rn%3), CASE WHEN rn<=20000 THEN 'CUST-HOT' ELSE CONCAT('CUST-',RIGHT('00000'+CONVERT(varchar(5),rn%6000),5)) END, CASE rn%4 WHEN 0 THEN 'OPEN' WHEN 1 THEN 'ASSIGNED' WHEN 2 THEN 'CLOSED' ELSE 'ESCALATED' END, 1+(rn%5), DATEADD(minute,-rn,'2026-08-20T12:00:00'), DATEADD(minute,30-rn,'2026-08-20T12:00:00'), CASE WHEN rn%4=2 THEN DATEADD(minute,90-rn,'2026-08-20T12:00:00') END, N'Capstone deterministic work order '+CONVERT(nvarchar(20),rn), CASE rn%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END FROM n;END;GOSELECT COUNT_BIG(*) AS rows_loaded,COUNT(DISTINCT customer_code) AS customers FROM ops.WorkOrder;The hot customer creates skew deliberately so parameter-sensitive behavior and cardinality mistakes are possible. This is a teaching workload, not a claim that 50,000 rows represents production scale.
2. Capture Query Store and current-plan evidence
USE ServiceHubCapstone;GOCREATE OR ALTER PROCEDURE ops.GetCustomerOrders @customer_code varchar(16)ASBEGIN SET NOCOUNT ON; SELECT work_order_id,status,priority,opened_at,region_code FROM ops.WorkOrder WHERE customer_code=@customer_code ORDER BY opened_at DESC;END;GOEXEC ops.GetCustomerOrders @customer_code='CUST-HOT';EXEC ops.GetCustomerOrders @customer_code='CUST-00017';GOSELECT TOP(20) q.query_id,p.plan_id,rs.count_executions,rs.avg_duration,rs.avg_cpu_time,rs.avg_logical_io_readsFROM sys.query_store_query AS qJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idWHERE q.object_id=OBJECT_ID(N'ops.GetCustomerOrders')ORDER BY rs.last_execution_time DESC;Do not read Query Store averages as individual-request latency. Aggregate over comparable intervals, inspect plan IDs/parameters, and combine with application percentiles.
3. Correlate resource evidence before changing anything
SELECT session_id,request_time,grant_time,requested_memory_kb,granted_memory_kb,used_memory_kbFROM sys.dm_exec_query_memory_grants;SELECT session_id,user_objects_alloc_page_count,internal_objects_alloc_page_count, user_objects_dealloc_page_count,internal_objects_dealloc_page_countFROM tempdb.sys.dm_db_session_space_usageWHERE session_id=@@SPID;SELECT TOP(15) wait_type,waiting_tasks_count,wait_time_ms,signal_wait_time_msFROM sys.dm_os_wait_statsORDER BY wait_time_ms DESC;SELECT DB_NAME(vfs.database_id) AS database_name,mf.name, vfs.num_of_reads,vfs.io_stall_read_ms,vfs.num_of_writes,vfs.io_stall_write_msFROM sys.dm_io_virtual_file_stats(DB_ID(N'ServiceHubCapstone'),NULL) AS vfsJOIN sys.master_files AS mf ON mf.database_id=vfs.database_id AND mf.file_id=vfs.file_id;These counters have different reset/scope semantics. The purpose is to correlate a hypothesis, not select “the highest number” and tune it.
4. One design change at a time
If dispatch reporting scans large portions of WorkOrder, a nonclustered columnstore index can be tested because columnstore is available across SQL Server 2025 editions, though advanced scale characteristics vary. If the problem is a latch/lock-heavy hot table with extreme concurrency, In-Memory OLTP can be evaluated—but it adds distinct durability, memory and feature constraints. Neither should be added merely because the server has CPU headroom.
USE ServiceHubCapstone;GO-- OPTIONAL experiment. Capture baseline first.IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'ops.WorkOrder') AND name=N'NCCI_cap_WorkOrder_Analytics') CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_cap_WorkOrder_Analytics ON ops.WorkOrder(region_code,status,priority,opened_at,technician_id);GOSELECT region_code,status,COUNT_BIG(*) AS orders,AVG(CONVERT(decimal(10,2),priority)) AS avg_priorityFROM ops.WorkOrderWHERE opened_at >= '2026-01-01'GROUP BY region_code,status;GO-- Compare actual plan, STATISTICS IO/TIME, Query Store and OLTP write overhead locally.-- DROP INDEX NCCI_cap_WorkOrder_Analytics ON ops.WorkOrder; -- rollback after experiment.For In-Memory OLTP, Chapter 20’s candidate analysis should be reused: prove lock/latch or execution overhead first, estimate XTP memory, then test a disposable migration. The capstone deliberately records “not chosen” as a valid engineering outcome.
5. Record the experiment, including rejected options
USE ServiceHubCapstone;INSERT governance.ArchitectureDecision(decision_name,chosen_option,rejected_options,rationale,evidence_needed,residual_risk)VALUES(N'Capstone workload tuning', N'Keep targeted rowstore indexes; use Query Store to govern regressions', N'Global MAXDOP change; rebuild-everything schedule; automatic migration to In-Memory OLTP', N'Current lab evidence does not prove those broader changes improve ServiceHub SLOs.', N'Representative concurrency test + application latency percentiles + Query Store/waits + resource counters.', N'Analytical growth may justify columnstore/replica separation later; re-benchmark as workload changes.');SELECT TOP(5) * FROM governance.ArchitectureDecision ORDER BY decision_id DESC;Check your understanding
- Why is a 50,000-row lab not a production benchmark?
- What does Query Store add over transient DMVs?
- When is columnstore a reasonable candidate?
- When is In-Memory OLTP a reasonable candidate?
- Why record rejected optimizations?
Review the answers
1. Hardware, concurrency, cache, storage, data shape and scale differ; results are local observations.
2. Persisted plan/runtime/wait history that can survive the immediate incident window.
3. For analytical scans/aggregations where segment compression/elimination/batch processing fit the workload, after measuring write and maintenance tradeoffs.
4. When evidence shows suitable hot/concurrent workload behavior and its durability/memory/feature constraints are acceptable.
5. So future operators understand what was tested, what evidence was missing, and when re-evaluation is warranted.