Indexes and performance
IO, timing, Query Store and parameters
Establish a baseline, then change one thing.
Understand the concept
STATISTICS IO reports access measurements such as logical reads; STATISTICS TIME reports CPU and elapsed time. Cache state, parallelism and other workloads affect results. A millisecond difference in one execution does not establish an improvement. Compare the same data, parameters and conditions. Never clear production caches for a casual experiment.
Query Store retains query, plan and runtime history; enabling it, storage and retention are operational decisions. It helps investigate regressions and parameter-sensitive workloads. SQL Server 2022 Parameter Sensitive Plan optimisation depends on version and compatibility; do not expect it on 2019. RECOMPILE, plan forcing and OPTIMIZE FOR are measured interventions, not default cures.
Support performance advice with measurements.
Clearing a shared server’s cache for a benchmark.
One topic. Three levels of depth.
SET STATISTICS IO ON;
SELECT ProductName FROM lab.Product WHERE ProductId = 1;
SET STATISTICS IO OFF;Inspect reads in the SSMS Messages tab.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Record IO and timing for customer two.
Show the solution and explanation
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OrderId FROM lab.SalesOrder WHERE CustomerId = 2;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Timings may be noisy on tiny data; also validate results and reads.