Skip to content

C# Interview Guide: SQL Server Query Optimisation — Execution Plans, Statistics, Query Store

Q18 — Query Optimisation Workflow — Execution Plans, Statistics, Query Store Level: Senior | Topic: SQL Server The Optimisation Workflow Effective query optimisation follows a structured workflow: identify slow queries, ...

Ekanatha Reddy Urivakili8 min870 words
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 regression

Figure 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

TechniqueProsConsWhen to Use
Covering IndexEliminates key lookups; fast data retrievalIncreased index maintenance overheadQueries with frequent Key Lookup operators
Query Hints (OPTIMIZE FOR)Mitigates parameter sniffing effectivelyCan reduce plan flexibility; requires testingParameter sniffing causing inconsistent perf
STATISTICS IO and TIMEPrecise IO & CPU metrics for benchmarkingAdds overhead; not suitable for production logsDevelopment and testing performance tuning
Query StorePersistent performance tracking & regression controlRequires additional storage; needs enablingAll production DBs to monitor regressions
Recompilation hintsForces new plan generated per executionPotential CPU overhead and instabilityRare 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

MetricCategoryDescription
Logical ReadsRED (Request)Number of logical pages read per query
CPU TimeRED (Request)CPU time consumed per query
Plan RecompilationsUSE (Resource)Number of query plan recompilations
Query DurationRED (Request)Wall-clock duration per query
Query Store Plan CountUSE (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

SLITarget / SLO
95th percentile query duration< 150 ms
Average logical reads per queryMaintain ≤ baseline established
Query store plan regressions0 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:

  1. Parsing and Binding
    • The query is parsed into a syntax tree, and SQL Server verifies object names, data types, and permissions.
  2. Algebrization and Semantic Checks
    • Logical expressions like joins, filters, and projections are formed.
  3. Optimization
    • The Query Optimizer enumerates candidate physical execution plans to execute the logical query. This is the most computationally complex phase.
  4. 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.
  5. 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:

OperatorPurpose
Index SeekEfficiently find matching rows using an index
Index ScanRead all rows in an index
Table ScanRead all rows in a heap or clustered index
Key LookupRetrieve non-indexed columns for rows found via index
Nested Loops JoinGood for small outer inputs, seeks inner indexes
Hash JoinSuitable for large, unindexed inputs
Merge JoinEfficient 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 parameter OPTIMIZE 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 FULLSCAN on volatile tables.
  • Use AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC settings 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 UNKNOWN for ad-hoc parameterized SQL.
  • Enable Optimize for Ad Hoc Workloads at 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_stats for 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

QuestionActionNotes
Is the query doing a table scan?Add suitable indexesPrefer covering or filtered indexes
Are key lookups frequent?Add INCLUDE columns to existing indexesBalances index size and query speed
Does estimated vs actual row count mismatch vastly?Update statistics with FULLSCAN, recompile, consider query rewriteIndicates stale stats or complex predicates
Is parameter sniffing causing instability?Use OPTIMIZE FOR UNKNOWN or RECOMPILE hintsTest in staging; could increase CPU overhead
Are query runtimes regressing in production?Enable Query Store and force known good plansEnables 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 recompilationsCould signal dynamic SQL or volatile stats updates
Are IO waits the bottleneck?Check server I/O subsystem; add instant file initializationHardware 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.


References