SQL Server Execution Plans — Reading the Query Engine's Work

An execution plan is SQL Server's explanation of HOW it executed a query.
It shows: which operations ran, which indexes were used, how many rows were processed,
and where the cost was concentrated.

Key operators you will encounter:

Clustered Index Scan    → Read ALL rows in the clustered index (table scan equivalent)
                          Warning: usually means a missing index

Index Seek              → Jump directly to matching rows using an index B-tree
                          Good: this is what you want

Key Lookup              → After an index seek, fetch extra columns from the clustered index
                          Warning: your index doesn't INCLUDE all needed columns

Hash Match (Join)       → Builds a hash table in memory to join tables
                          OK for large sets; consider merge join or nested loops for small sets

Sort                    → Explicit sort operation — often causes memory spills
                          Fix: ORDER BY a column covered by an existing index

Nested Loops Join       → For each row in the outer table, look up in the inner table
                          Good for small sets; bad for large sets (exponential cost)

Parallelism             → Query split across CPU cores (DOP > 1)
                          Can help or hurt depending on query type

Reading the plan:
  → Arrows show data flow — thick arrows = many rows
  → Percentage on each operator = cost relative to total query
  → Right to left, bottom to top = execution order
  → Find the most expensive operator (highest %) and fix that first

-- Actual plan (runs the query, captures real row counts):
-- In SSMS: Query menu → Include Actual Execution Plan, then run your query

-- Estimated plan (does not run query — uses statistics):
SET SHOWPLAN_ALL ON;
SELECT p.MedicationName, p.InrValue
FROM   Prescriptions p
WHERE  p.PatientMrn = 'MRN001'
  AND  p.Status     = 'Approved';
SET SHOWPLAN_ALL OFF;

-- Query Store (production query plans — no need to reproduce the issue):
-- View in SSMS: database → Query Store → Top Resource Consuming Queries
-- Or query directly:
SELECT TOP 20
    qst.query_sql_text,
    qsp.avg_duration          / 1000.0 AS avg_duration_ms,
    qsp.avg_logical_io_reads          AS avg_reads,
    qsp.execution_count,
    qsp.plan_id
FROM sys.query_store_query_text qst
JOIN sys.query_store_query       qsq ON qsq.query_text_id = qst.query_text_id
JOIN sys.query_store_plan        qsp ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_runtime_stats qsr ON qsr.plan_id = qsp.plan_id
ORDER BY qsp.avg_duration DESC;

// Log all SQL generated by EF Core — see what queries are being sent

builder.Services.AddDbContext<ClinicalDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .LogTo(
               sql => logger.LogDebug("EF Core SQL: {Sql}", sql),
               LogLevel.Debug)
           .EnableSensitiveDataLogging(env.IsDevelopment()); // shows parameter values
});

// Common EF Core anti-pattern: N+1 queries
// BAD — generates 1 query per prescription (N+1):
var prescriptions = await _context.Prescriptions
    .Where(p => p.PatientMrn == mrn)
    .ToListAsync(ct);

foreach (var prescription in prescriptions)
{
    var prescriber = await _context.Prescribers  // separate query per prescription!
        .FindAsync(prescription.PrescriberId);
}

// GOOD — single JOIN query:
var prescriptions = await _context.Prescriptions
    .Include(p => p.Prescriber)  // generates LEFT JOIN in SQL
    .Where(p => p.PatientMrn == mrn)
    .ToListAsync(ct);

// Better for read models — project to DTO, avoid loading full entities:
var summaries = await _context.Prescriptions
    .Where(p => p.PatientMrn == mrn && p.Status == "Approved")
    .Select(p => new PrescriptionSummaryDto(
        p.Id,
        p.MedicationName,
        p.Prescriber.FullName,  // Join handled by EF Core
        p.ApprovedAt))
    .ToListAsync(ct);

-- Problem 1: Table Scan (Clustered Index Scan) instead of Index Seek
-- Cause: no useful index, or query is using a non-SARGable predicate

-- NON-SARGable (can't use index — function applied to indexed column):
SELECT * FROM Prescriptions
WHERE YEAR(CreatedAt) = 2026;   -- function on indexed column → scan

-- SARGable (can use index seek):
SELECT * FROM Prescriptions
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2027-01-01';

-- NON-SARGable (implicit conversion — PatientMrn is NVARCHAR, parameter is VARCHAR):
-- EF Core: always use the right type — NVARCHAR columns need N'' prefix or typed params

-- Problem 2: Key Lookup (RID Lookup)
-- Cause: index doesn't include all columns in SELECT
-- Fix: add INCLUDE columns to the index

-- BEFORE: IX_Prescriptions_PatientMrn on (PatientMrn) — no INCLUDE
-- Plan: Index Seek → Key Lookup (to get MedicationName, InrValue)
SELECT MedicationName, InrValue
FROM   Prescriptions
WHERE  PatientMrn = 'MRN001';

-- AFTER: Add INCLUDE:
CREATE INDEX IX_Prescriptions_PatientMrn
    ON Prescriptions (PatientMrn)
    INCLUDE (MedicationName, InrValue);
-- Plan: Index Seek only — no key lookup

-- Problem 3: Sort operation with memory spill
-- Check: Actual Execution Plan → Sort operator → right-click → Properties → Memory Spill Level
-- Fix: add an index that pre-sorts on the ORDER BY column

-- Problem 4: Parameter sniffing (plan optimised for first-run parameters, bad for others)
SELECT * FROM Prescriptions WHERE PatientMrn = @mrn;
-- Compiled for MRN001 (1 prescription) — plan is Nested Loops
-- Called with MRN999 (10,000 prescriptions) — Nested Loops is terrible
-- Fix option 1: OPTION (RECOMPILE) — recompile every execution
-- Fix option 2: OPTION (OPTIMIZE FOR UNKNOWN) — neutral plan
-- Fix option 3: split into different queries for different input patterns

-- EF Core Include() generates a LEFT JOIN — see how SQL Server handles it

-- Query: Get all approved prescriptions with prescriber name and ward
SELECT
    p.Id,
    p.MedicationName,
    p.InrValue,
    pr.FullName AS PrescriberName,
    w.Name      AS WardName
FROM Prescriptions p
LEFT JOIN Prescribers pr ON pr.Id = p.PrescriberId
LEFT JOIN Wards       w  ON w.Id  = p.WardId
WHERE p.Status = 'Approved'
  AND p.WardId = @wardId;

-- Execution plan analysis:
-- 1. Clustered Index Seek on Prescriptions (WardId + Status filtered index) → good
-- 2. Nested Loops Join to Prescribers (seek by PrescriberId) → good (small inner set)
-- 3. Nested Loops Join to Wards (seek by WardId) → good

-- If plan shows Hash Join instead of Nested Loops for small tables:
-- → Statistics may be out of date: UPDATE STATISTICS Prescriptions
-- → Or cardinality estimate is wrong: check actual vs estimated row counts in plan

-- Enable Query Store (persist execution plans and runtime stats):
ALTER DATABASE ClinicalDb
SET QUERY_STORE = ON (
    OPERATION_MODE           = READ_WRITE,
    MAX_STORAGE_SIZE_MB      = 1000,
    INTERVAL_LENGTH_MINUTES  = 60,
    QUERY_CAPTURE_MODE       = AUTO   -- only captures significant queries
);

-- Find regressed queries (recent plans slower than historical best):
SELECT TOP 10
    qst.query_sql_text,
    regressed.avg_duration_ms,
    baseline.avg_duration_ms  AS best_avg_duration_ms,
    regressed.avg_duration_ms
        - baseline.avg_duration_ms AS regression_ms
FROM sys.query_store_query_text qst
-- (Query Store regression detection is available in SSMS via GUI)
-- Use SSMS: Query Store → Regressed Queries for visual plan comparison

-- Force a known-good plan when regression is detected:
EXEC sys.sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 7;