title: >
C# Interview Guide: SQL Server Query Optimisation — Execution Plans, Statistics, Query Store
excerpt: >
Master SQL Server query optimisation with this detailed guide on identifying bottlenecks using execution plans, statistics, and Query Store to ensure peak query performance.
tags:
- sql-server
- query-optimisation
- execution-plans
- query-store
- performance-tuning
seoTitle: >
SQL Server Query Optimisation: Execution Plans, Stats & Query Store Guide
seoDescription: >
Learn a structured SQL Server query optimisation workflow using execution plans, statistics, and Query Store to identify and fix query performance issues.
category: system-design
---
## Q18 — Query Optimisation Workflow — Execution Plans, Statistics, Query Store
*Level: Senior | Topic: SQL Server*
Optimising SQL Server queries effectively involves understanding and interpreting the _cost_ of query operations in depth. Execution Plans, Statistics IO & TIME, and Query Store are foundational tools in diagnosing, fixing, and monitoring query performance.
This article expands the basics into a comprehensive workflow, enriched with diagrams, code examples, trade-off analysis, and observability practices that every senior engineer must master.
---
## The Query Optimisation Workflow: High-Level Design
The optimisation process is a cycle of:
- Identifying costly queries
- Examining execution plans and statistics
- Diagnosing specific bottlenecks
- Applying targeted fixes (indexes, query hints)
- Measuring improvements quantitatively
- Monitoring regressions in production with Query Store
```mermaid
flowchart LR
A[Identify Expensive Queries] --> B[Obtain Actual Execution Plans]
B --> C{Bottleneck Diagnosis?}
C -->|Table Scans| D[Add/Adjust Indexes]
C -->|Parameter Sniffing| E[Apply Query Hints]
C -->|Cardinality Estimates| F[Update Statistics / Rewrite Query]
D & E & F --> G[Measure Improvement via Statistics IO & Time]
G --> H[Deploy Changes]
H --> I[Monitor Using Query Store]
I --> A
Figure 1: Structured workflow for SQL Server query optimisation.
Step-by-Step Approach in Detail
Step 1: Identify Top Costly Queries
Pinpoint queries consuming the most CPU or IO resources by querying sys.dm_exec_query_stats. Focus efforts on queries with highest average logical reads or CPU time.
Step 2: Retrieve Actual Execution Plans
Using SSMS or SQL Server APIs, get the actual execution plan while the query runs. This plan reflects real runtime decisions, including row counts and operators used (index seek, scan, key lookup).
Look for:
- Table scans indicating missing indexes
- Key lookup operators suggesting a need for covering indexes
- Large mismatches between estimated and actual row counts (cardinality misestimates)
Step 3: Measure IO & CPU Cost Before and After Fixes
Turn on statistics IO and TIME:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
Execute the query and note logical reads, physical reads, and CPU time metrics.
Step 4: Apply Indexes or Rewrite Query
If you observe Key Lookups or table scans causing excessive IO, consider creating covering or filtered indexes.
Step 5: Handle Parameter Sniffing Issues
Parameter sniffing causes SQL Server to generate plans optimized for initial parameter values that may be suboptimal for others. Use:
OPTION (OPTIMIZE FOR (@param UNKNOWN))
to force use of average distribution plans.
Step 6: Monitor and Enforce Plan Stability with Query Store
Enable Query Store to capture query performance history and force regressions back to known good plans without deploying code changes.
ALTER DATABASE YourDb SET QUERY_STORE = ON;
Execution and Interaction Sequence
Consider a typical optimisation interaction between the DBA/Developer, SQL Server engine, and tools:
sequenceDiagram
participant Dev as Developer/DBA
participant SQL as SQL Server
participant SSMS as SSMS/Query Store UI
Dev->>SQL: Query sys.dm_exec_query_stats to identify top queries
Dev->>SQL: Request actual execution plan (SET STATISTICS or Ctrl+M)
SQL-->>Dev: Return plan with operator details and actual row counts
Dev->>SQL: Test index creation / query hints
SQL-->>Dev: Show updated execution plan and statistics
Dev->>SQL: Enable Query Store for monitoring
SSMS->>SQL: Visualise Query Store metrics and force plans
Dev->>SSMS: Enforce known good plan on regressionFigure 2: Interaction sequence during query optimisation.
Key SQL Scripts and Code Examples
Minimal Example: Finding Expensive Queries by Logical Reads
SELECT TOP 10
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 QueryText,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.total_worker_time / qs.execution_count AS AvgCpuMicroseconds,
qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgLogicalReads DESC;
Notice how this identifies hot queries by average logical IO, a reliable resource cost metric independent of transient runtime conditions.
Realistic Example: Apply Covering Index and Fix Parameter Sniffing
-- Before: Query with parameter sniffing problem
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus @Status NVARCHAR(20) AS
BEGIN
SELECT OrderId, CustomerId, PlacedAt
FROM dbo.Orders
WHERE Status = @Status
ORDER BY PlacedAt;
END;
GO
-- Identified with Key Lookup and high logical reads
-- Add a covering index to avoid key lookup
CREATE NONCLUSTERED INDEX IX_Orders_Status_PlacedAt
ON dbo.Orders (Status, PlacedAt)
INCLUDE (OrderId, CustomerId);
GO
-- Fix parameter sniffing with query hint
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus @Status NVARCHAR(20) AS
BEGIN
SELECT OrderId, CustomerId, PlacedAt
FROM dbo.Orders
WHERE Status = @Status
ORDER BY PlacedAt
OPTION (OPTIMIZE FOR (@Status UNKNOWN));
END;
This example demonstrates a combined approach: adding a covering index reduces physical IO, while the OPTIMIZE FOR UNKNOWN hint stabilises plan choice across varying parameter values.
Trade-offs in Query Optimisation Techniques
| Technique | Pros | Cons | When to Use |
|---|---|---|---|
| Covering Index | Eliminates key lookups; fast data retrieval | Increased index maintenance overhead | Queries with frequent Key Lookup operators |
| Query Hints (OPTIMIZE FOR) | Mitigates parameter sniffing effectively | Can reduce plan flexibility; requires testing | Parameter sniffing causing inconsistent perf |
| STATISTICS IO and TIME | Precise IO & CPU metrics for benchmarking | Adds overhead; not suitable for production logs | Development and testing performance tuning |
| Query Store | Persistent performance tracking & regression control | Requires additional storage; needs enabling | All production DBs to monitor regressions |
| Recompilation hints | Forces new plan generated per execution | Potential CPU overhead and instability | Rare cases with highly variable data patterns |
Observability for SQL Query Optimisation
Tracking, alerting, and tracing best practices close the optimisation feedback loop.
What to Log
- Query text or query_id (obfuscated if needed)
- Execution counts
- Logical and physical reads per execution
- CPU time per execution
- Execution plan baselines (query plan hashes)
- Plan changes and forced plan usage events
Sample Structured Log Entry (JSON)
{
"query_id": 12345,
"query_hash": "0xABCDEF123456",
"execution_count": 100,
"avg_logical_reads": 120,
"avg_cpu_time_ms": 15,
"plan_id": 42,
"forced_plan": true,
"timestamp": "2024-06-01T14:22:30Z"
}
Key Metrics
| Metric | Category | Description |
|---|---|---|
| Logical Reads | RED (Request) | Number of logical pages read per query |
| CPU Time | RED (Request) | CPU time consumed per query |
| Plan Recompilations | USE (Resource) | Number of query plan recompilations |
| Query Duration | RED (Request) | Wall-clock duration per query |
| Query Store Plan Count | USE (Resource) | Number of plans per query |
Distributed Tracing with OpenTelemetry (C# Example)
Instrument query execution times and capture query text hashes as attributes:
using OpenTelemetry.Trace;
using System.Data.SqlClient;
// Initialize tracer provider in app startup (example)
var tracerProvider = Sdk.CreateTracerProviderBuilder()
.AddSource("MyApp.SQL")
.AddConsoleExporter()
.Build();
var tracer = tracerProvider.GetTracer("MyApp.SQL");
string queryText = "SELECT * FROM Orders WHERE Status = @status";
string connectionString = "...";
using (var activity = tracer.StartActivity("SQL Query Execution"))
{
activity?.SetTag("db.system", "mssql");
activity?.SetTag("db.statement", queryText);
activity?.SetTag("db.operation", "SELECT");
activity?.SetTag("db.sql.query_hash", queryText.GetHashCode());
using var conn = new SqlConnection(connectionString);
using var cmd = new SqlCommand(queryText, conn);
cmd.Parameters.AddWithValue("@status", "Pending");
conn.Open();
cmd.ExecuteReader();
}
Adding tracing to SQL queries empowers end-to-end latency analysis correlated with other system components.
SLI and SLO Table for Query Performance
| SLI | Target / SLO |
|---|---|
| 95th percentile query duration | < 150 ms |
| Average logical reads per query | Maintain ≤ baseline established |
| Query store plan regressions | 0 sustained regressions per month |
| Forced plan usage success rate | > 99% |
Deep Dive: How SQL Server Generates and Uses Execution Plans Internally
Understanding the internal workings of execution plans clarifies why some query patterns degrade performance and how targeted interventions help.
Compilation and Optimization Stages
When SQL Server receives a query, it undergoes multiple internal steps:
- Parsing and Binding
- The query is parsed into a syntax tree, and SQL Server verifies object names, data types, and permissions.
- Algebrization and Semantic Checks
- Logical expressions like joins, filters, and projections are formed.
- Optimization
- The Query Optimizer enumerates candidate physical execution plans to execute the logical query. This is the most computationally complex phase.
- Plan Selection
- Cost estimation is done for each candidate plan based on statistics, indexes, and row counts.
- The plan with the lowest estimated cost (in CPU, IO, memory) is chosen.
- Caching
- The plan is stored in the plan cache keyed by query hash and parameters to be reused for subsequent executions.
Cost Model and Statistics
The optimizer relies heavily on statistics metadata representing data distribution for tables and indexes:
- Histograms showing value frequencies for columns
- Density vectors for multi-column correlations
- Row counts and page counts
These statistics feed into cost formulas estimating:
- CPU cost of filter and join operations
- IO cost (logical reads) of accessing pages
- Memory and spill costs
If statistics are stale or missing, the optimizer's estimates will be poor, resulting in suboptimal plans.
Plan Operators and Execution Strategies
Key physical operators include:
| Operator | Purpose |
|---|---|
| Index Seek | Efficiently find matching rows using an index |
| Index Scan | Read all rows in an index |
| Table Scan | Read all rows in a heap or clustered index |
| Key Lookup | Retrieve non-indexed columns for rows found via index |
| Nested Loops Join | Good for small outer inputs, seeks inner indexes |
| Hash Join | Suitable for large, unindexed inputs |
| Merge Join | Efficient for sorted inputs on join keys |
Understanding the operators lets you interpret execution plans effectively and decide remedies such as adding indexes or rewriting joins.
Plan Cache and Recompilation
- Plans remain cached and reused unless the optimizer detects cardinality changes or schema updates triggering recompilation.
- Frequent recompilations can cause CPU overhead (tracked by
sys.dm_exec_query_stats.recompile_count). - Parameter sniffing occurs because the initial plan is compiled for parameter values seen at first execution, potentially suboptimal for others.
Production Scenario: End-to-End Query Performance Diagnosis and Fix
This section walks through a practical scenario illustrating diagnosis and optimisation of a high-latency report query impacting a critical financial service.
Scenario Context
- Financial application executing a monthly report:
GetInvoiceSummary. - Complaints from users about slow response (avg runtime 10 seconds, spikes to 30s).
- Observed increased IO wait times and CPU spikes on the SQL Server instance.
Step 1: Identify Expensive Queries in Production
Run:
SELECT TOP 5
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 QueryText,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.total_worker_time / qs.execution_count / 1000.0 AS AvgCpuMs,
qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgLogicalReads DESC;
GetInvoiceSummary emerges as top IO consumer with ~200k logical reads per execution.
Step 2: Capture Actual Execution Plan
Enable actual plans via SSMS or extended events. Analyze operators:
- Notice a Table Scan on
InvoiceDetails. - Key Lookup operator fetching non-key columns.
- Estimated row counts = 5,000 vs actual rows = 150,000 — clear cardinality misestimate.
Step 3: Check and Update Statistics
-- Check last updated date for statistics on InvoiceDetails
EXEC sp_helpstats 'InvoiceDetails', 'ALL';
-- Update if stale (older than few days or months depending on data churn)
UPDATE STATISTICS InvoiceDetails WITH FULLSCAN;
Post-update, recompile query and compare cardinality estimates.
Step 4: Add a Covering Index
From the plan and missing index DMVs, SQL Server recommends:
CREATE NONCLUSTERED INDEX IX_InvoiceDetails_InvoiceID_Status
ON InvoiceDetails (InvoiceID, Status)
INCLUDE (Amount, Tax);
This eliminates the Key Lookup causing lookup IO overhead.
Step 5: Rewrite Query to Avoid Unnecessary Sorting
The original query had ORDER BY Amount DESC, but the business only requires sorting by InvoiceDate.
Change:
ORDER BY InvoiceDate DESC;
to allow index order advantages.
Step 6: Use Query Store to Monitor
Query Store is enabled and configured:
ALTER DATABASE FinancialDB SET QUERY_STORE = ON;
ALTER DATABASE FinancialDB SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);
ALTER DATABASE FinancialDB SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 100);
Post deployment, track if plan regressions occur, and force previous good plan if required:
EXEC sp_query_store_force_plan @query_id = QUERY_ID, @plan_id = PLAN_ID;
Outcome
- Logical reads dropped from 200k to 20k per execution.
- Average CPU time per query reduced from 800ms to 150ms.
- Query runtime stabilized around 1.8 seconds.
- No significant recompilation spikes observed.
- Query Store traces confirm no regressions were introduced.
Edge Cases, Failure Modes and Pitfalls in Query Optimisation
Robust query tuning requires anticipating uncommon but impactful situations that cause unexpected behaviour.
Parameter Sniffing Variants
-
Good Sniff, Bad Sniff
Initial compilation uses a parameter value leading to an efficient plan, but subsequent executions with different values degrade drastically. -
One-time Compilation for Skewed Data
Uniform distribution assumption breaks due to skew; average plan performs poorly for minority parameter values.
Mitigations:
OPTION (RECOMPILE)or forced parameterOPTIMIZE FOR (@param UNKNOWN)hints- Using local variables inside the procedure (can degrade estimates)
- Plan guides or forced plans in Query Store
Outdated or Missing Statistics
- Leads to wildly inaccurate cardinality estimates and poor plans.
- Automatic Stats update threshold (20% change in a table) might be insufficient for high-churn tables.
Mitigations:
- Schedule frequent manual
UPDATE STATISTICS WITH FULLSCANon volatile tables. - Use
AUTO_CREATE_STATISTICSandAUTO_UPDATE_STATISTICS_ASYNCsettings wisely.
Indexing Overhead
- Indexes speed reads but slow writes (INSERT/UPDATE/DELETE).
- Excessive indexes increase storage and maintenance windows.
Mitigations:
- Analyze workload patterns (read-heavy vs write-heavy)
- Use filtered indexes or indexed views selectively.
- Regularly monitor index fragmentation and rebuild/reorganize.
Plan Cache Pollution
- Ad-hoc queries or literal value queries generate many single-use plans.
- Causes cache bloat and evictions of useful plans.
Mitigations:
- Use parameterized queries or stored procedures.
- Set
OPTIMIZE FOR UNKNOWNfor ad-hoc parameterized SQL. - Enable
Optimize for Ad Hoc Workloadsat the SQL Server instance level.
Long-running Queries Blocking Others
- Query plans might produce spill to tempdb due to memory pressure.
Mitigations:
- Monitor
sys.dm_os_wait_statsfor PAGEIOLATCH waits. - Increase server memory or tune query to reduce memory grants.
- Ensure proper indexes to avoid sorts or hashes spilling to disk.
Performance Considerations and Scalability
Modern production databases face continuously growing data volumes and workload concurrency. Optimising queries with scalability in mind is essential.
IO vs. CPU Trade-offs
- Some query hints (e.g.
FORCE ORDER) or plans might minimize IO but increase CPU (hash join instead nested loop). - Optimal balancing depends on your server profile (fast CPU, slow disk; or vice versa).
Parallelism
- SQL Server will parallelize expensive queries if cost threshold is exceeded.
- Parallel plans reduce duration but increase CPU usage overall.
Monitor:
- CXPACKET waits — excessive waiting on parallel threads
- Query timeout or blocking caused by parallelism
Tune:
EXEC sp_configure 'max degree of parallelism', 4;
EXEC sp_configure 'cost threshold for parallelism', 50;
Parameterization and Forced Plans
- Use parameterization carefully to enable shared plans but avoid forcing plans that misfit certain parameter value ranges.
- Query Store's plan forcing functionality helps keep performance stable but requires periodic validation.
Hardware and Storage Level Influence
- SSDs reduce IO latency, mitigating some heavy table scans.
- Memory pressure can cause spills and recompilations; monitor
sys.dm_os_memory_cache_counters.
Decision Checklist: When and How to Apply Each Technique
| Question | Action | Notes |
|---|---|---|
| Is the query doing a table scan? | Add suitable indexes | Prefer covering or filtered indexes |
| Are key lookups frequent? | Add INCLUDE columns to existing indexes | Balances index size and query speed |
| Does estimated vs actual row count mismatch vastly? | Update statistics with FULLSCAN, recompile, consider query rewrite | Indicates stale stats or complex predicates |
| Is parameter sniffing causing instability? | Use OPTIMIZE FOR UNKNOWN or RECOMPILE hints | Test in staging; could increase CPU overhead |
| Are query runtimes regressing in production? | Enable Query Store and force known good plans | Enables fast rollback without deployment |
| Does the workload have many ad-hoc queries? | Parameterize queries or enable “Optimize for Ad Hoc” | Prevent plan cache pollution |
| Is there excessive recompilation? | Identify frequent schema or stats changes; reduce recompilations | Could signal dynamic SQL or volatile stats updates |
| Are IO waits the bottleneck? | Check server I/O subsystem; add instant file initialization | Hardware upgrade might be needed |
Each step must be validated by measuring before-and-after statistics and carefully observing overall system impact.
Advanced Observability: Correlating Plan Changes with Application Deployments
Production query performance can shift following schema changes, deployments, or index rebuilds. Observability practices enhance root cause analysis.
Integrate Query Store Metrics with CI/CD Pipelines
- Using Query Store DMVs, capture plan changes and performance shifts around deployment timestamps.
- Automate alerts on plan regression rates post-deploy.
SELECT
qsp.query_id,
qsp.plan_id,
qsp.last_execution_time,
qsp.avg_duration,
q.text AS QueryText,
qp.plan_handle
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text q ON qsq.query_text_id = q.query_text_id
JOIN sys.dm_exec_cached_plans cp ON qsp.plan_handle = cp.plan_handle
ORDER BY qsp.last_execution_time DESC;
Augment Logs with Deployment Metadata
- Include release version and feature flags as structured log fields along with query performance data.
- Facilitates attribution of regressions to releases causing query plan changes.
Example Alerting Rule Using Log Queries
QueryPerformanceLogs
| where ForcedPlan == false
| summarize count_if(AvgDuration > 5000) by DeploymentVersion, bin(Timestamp, 1h)
| where count_if_AvgDuration_ > 5
| project DeploymentVersion, Timestamp
Alerts notify engineers to investigate plan regressions tied to new code or schema changes early.
This deep integration of query optimisation with deployment and observability workflows is a hallmark of production-grade database engineering.
Conclusion
Effective SQL Server query optimisation combines data-driven diagnosis with tactical fixes and robust monitoring via Query Store. Here are the key takeaways:
- Always start from identifying the most expensive queries logically (logical reads, CPU time).
- Use the actual execution plan to pinpoint bottlenecks such as table scans, key lookups, or cardinality issues.
- Measure IO and CPU impact with statistics commands before and after changes.
- Introduce covering indexes or rewrite queries where needed.
- Address parameter sniffing using query hints to stabilise plans.
- Enable Query Store in production for historical data, regression detection, and plan forcing.
- Instrument your queries with logs and distributed tracing to make the optimisation lifecycle observable.
- Define and monitor SLIs and SLOs aligned with business expectations to maintain performance health.
A systematic approach like this complements your deep SQL knowledge and will impress interviewers expecting production-grade expertise.