https://sqlmaster.mssqlplanner.com

Blog

  • SQL Server Architecture: SQLOS, Buffer Pool, and Plan Cache Explained

    SQL Server Architecture: SQLOS, Buffer Pool, and Plan Cache Explained

    Every performance problem eventually traces back to one of three resources: memory, CPU scheduling, or I/O. Understanding how SQL Server manages all three internally is the foundation this entire course builds on — every later module’s diagnostic technique is really a way of inspecting one of these three subsystems more closely.

    SQL Server Architecture, sketched out(memory, CPU, and I/O — everything traces back here)SQLOS — the scheduler underneath T-SQLmanages CPU threads (schedulers) + memory allocation for everything belowBuffer Poolcached 8KB data pages in RAMread from disk once, RAM afterPlan Cachecached compiled execution plansavoids recompiling same shapeDisk8KB pages on physical storagefirst read onlyNew query shape arrivescompiled once, plan cachedcompile once, reuseMore RAM only helps the BufferPool — CPU scheduling & plancache churn need a different fix. 📌

    SQLOS: The Layer Beneath T-SQL

    SQLOS is SQL Server’s own thin operating-system layer, sitting between the Windows/Linux OS and the relational engine. It manages scheduling (via non-preemptive “SQLOS schedulers” mapped roughly to CPU cores), memory allocation, and synchronization — SQL Server largely manages its own thread scheduling rather than leaving it entirely to the OS, which is why a CPU-bound SQL Server workload behaves differently from a typical application.

    -- See the schedulers directly — one row per logical CPU SQL Server is using
SELECT scheduler_id, cpu_id, status, is_online, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

    A consistently high runnable_tasks_count across schedulers is an early, concrete sign of genuine CPU pressure — more tasks are ready to run than there are schedulers to run them, so they queue.

    The Buffer Pool: Memory’s Biggest Consumer

    Buffer Pool Cached 8KB data pages Read from disk once, served from RAM after A page read from cache is orders of magnitude faster than from disk Plan Cache Cached compiled execution plans Avoids recompiling the same query shape repeatedly First parameter value compiled shapes the cached plan (parameter sniffing)

    Checking Buffer Pool Pressure

    SELECT COUNT(*) * 8 / 1024 AS cached_data_mb
FROM sys.dm_os_buffer_descriptors;

SELECT total_physical_memory_kb / 1024 AS total_ram_mb,
       available_physical_memory_kb / 1024 AS available_ram_mb
FROM sys.dm_os_sys_memory;

    Checking Plan Cache Health

    SELECT objtype, COUNT(*) AS plan_count, SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

    Why This Matters for Everything Ahead

    When a query is slow, the real question is always: is it waiting on disk I/O because the data wasn’t in the buffer pool? Is it recompiling because plan cache pressure evicted it? Or is CPU scheduling itself the constraint? Every later module — indexing, query optimization, monitoring — is really about managing these same three resources more efficiently.

    Common mistake: Treating “add more RAM” as a universal fix. More RAM only helps the buffer pool side of the equation — it does nothing for CPU scheduling pressure or plan cache churn from constantly-changing query shapes. This exact myth gets its own dedicated debunking in the Bonus module.
    Practice tip: Run the scheduler query above right now, on your own instance, and note the runnable_tasks_count for each scheduler. Come back to this same query after Module 4’s execution plan work and compare it against a query you know is CPU-intensive — seeing the number move is what makes “CPU-bound” a concrete, checkable fact instead of an abstract label.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • The Evidence-First Workflow: How to Actually Approach SQL Server Performance Tuning

    The Evidence-First Workflow: How to Actually Approach SQL Server Performance Tuning

    This course assumes you already know T-SQL well — SELECT, JOIN, indexes as objects, stored procedures — and is entirely about the layer above that: how to diagnose why something is slow, with evidence, rather than guessing. Before any module ahead, one methodology underlies all of them. “It feels slow, let’s add an index” is how performance tuning goes wrong — it’s the exact folklore-over-measurement failure mode the Bonus module’s Myth-Busting lesson catalogs in depth. Here’s the loop that actually works, and the one you’ll apply in every module ahead.

    Evidence-First, sketched out(the loop every module in this course uses)1. Baselinemeasure beforetouching anything2. CaptureEvidenceXE / Query Store3. Find theBottleneckCPU? I/O? Locks?4. Change ONEthingindex OR rewrite OR stats5. Validatere-run, compareto baselinerepeat the loop, every moduleChange 3 things at once and itgets faster? You’ve learnedNOTHING about which one worked. 📌

    The Five-Step Loop

    1. Baseline 2. Capture Evidence 3. Identify Bottleneck 4. Iterate 5. Validate

    1. Define Baseline

    Measure duration, CPU, and I/O before touching anything. Without a number to compare against, “it’s faster now” is a feeling, not evidence. This means running SET STATISTICS IO, TIME ON (or capturing the same via Query Store) against the current, unmodified state, and writing the numbers down somewhere you’ll actually compare against later — not just “remembering it felt slow.”

    2. Capture Evidence

    Use Extended Events or Query Store (both covered fully in Module 6) — not guesswork, not “I think it’s the JOIN.” Actual captured data about what ran, how long, and what it waited on.

    3. Identify the Bottleneck

    Interpret execution plans and wait statistics (Module 4 and Module 5) to find the actual constraint — CPU-bound, I/O-bound, or lock-bound are three very different problems with three very different fixes. Treating a lock-bound query with a new index, for instance, accomplishes nothing, because the bottleneck was never about read efficiency in the first place.

    4. Iterate: One Variable at a Time

    Change one thing. Add the index, or rewrite the predicate, or update statistics — not all three at once. If you change three things and it gets faster, you’ve learned nothing about which one mattered, and you’ve built a false intuition (“adding indexes always helps”) that will mislead your next diagnosis.

    5. Validate Against the Baseline

    Re-run the same measurement from step 1. Did it actually improve, by how much, and did anything else get worse (writes slower because of a new index, for instance — the exact index-maintenance cost tradeoff Module 2 covers)?

    Why This Discipline Matters

    Performance tuning without this loop turns into superstition — “we always rebuild indexes weekly” without evidence that fragmentation was ever the problem for that specific table. Every module in this course — architecture, indexing, query optimization, execution plans, locking, monitoring, advanced features — applies this exact loop to a different layer of the system. Module 3’s “13-Hour Delete” case study is this workflow followed start to finish against a real, painful incident, and is worth returning to once you’ve absorbed all five steps here.

    Practice tip: Before starting Module 1, write these five steps somewhere you’ll see them while working through the rest of this course. Every module’s exercises are designed around this loop — skipping the baseline step specifically is the most common way students undercut their own learning here, because it removes the only way to actually confirm a fix worked.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Index Fragmentation and Statistics Maintenance in SQL Server: REORGANIZE vs REBUILD

    Index Fragmentation and Statistics Maintenance in SQL Server: REORGANIZE vs REBUILD

    “Always rebuild indexes weekly” is one of the most common DBA myths in this entire field. Here’s what fragmentation and statistics actually require.

    Fragmentation & Stats, sketched out(measure — don’t schedule blindly)Healthy Index1234pages in physical order— one smooth scanFragmented Index1324pages scattered on disk— extra random I/O jumps< 5%5–30%> 30%do nothingREORGANIZE (online)REBUILDA tiny, always-seeked table at60% fragmentation? Doesn’t matter.Only large range-scanned tables care. 📌

    Measuring Fragmentation First

    This is the direct extension of Ch.8’s index-usage DMV lesson — same evidence-first instinct, now pointed at internal index health instead of usage frequency.

    SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
    ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

    The Standard Thresholds

    Fragmentation Action
    < 5% Do nothing
    5–30% ALTER INDEX ... REORGANIZE — online, lighter-weight
    > 30% ALTER INDEX ... REBUILD — heavier, can be ONLINE = ON in Enterprise

    The Myth, Directly Addressed

    “Always rebuild weekly” wastes CPU/IO on indexes that were never fragmented — measure first, every time

    A small, rarely-scanned table can sit at 60% fragmentation and never matter, because it’s tiny and always read via seeks. Fragmentation only meaningfully affects large tables read via range scans. Evidence-first applies here too: measure, don’t schedule blindly.

    Statistics: Often the Real Culprit

    -- Check when statistics were last updated and how many rows have changed since
SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name,
    sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) = 'YourTableName';

-- Manually refresh if auto-update hasn't triggered recently
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;

    By default, auto-update statistics fires after roughly 20% of rows change (with some newer, more granular thresholds on recent SQL Server versions for large tables). A query that suddenly gets a bad plan after a large bulk load is very often stale statistics, not fragmentation — check this DMV before reaching for a rebuild.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • SQL Server Index Strategy: Deciding What to Index Based on Real Query Patterns

    SQL Server Index Strategy: Deciding What to Index Based on Real Query Patterns

    Knowing index syntax isn’t the hard part — deciding what deserves an index is. This is strategy, not mechanics.

    Index Strategy, sketched out(what actually deserves an index)1. Missing Index DMVlogs a wish, not an order— verify before creating2. Weigh the Traderead benefit vs write costweighted by real frequency3. Decidecreate, keep, or dropRead Benefit ↑faster seeks, not scansfewer logical reads per run× how often the query runsvsWrite Cost ↓every INSERT/UPDATE/DELETEmust maintain this index× the table’s write frequencyZero reads + nonzero writes =pure cost. Drop it — unless it’senforcing a PK/UNIQUE. 📌

    Let SQL Server Tell You What It’s Missing

    SELECT
    d.statement AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns,
    s.user_seeks, s.avg_total_user_cost, s.avg_user_impact
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact * s.user_seeks DESC;

    This DMV logs every time the optimizer wished an index existed. It’s a starting hypothesis, not an automatic answer — verify against the Evidence-First workflow before creating anything.

    The Cost-Benefit That Actually Matters

    This is a direct application of Ch.8’s covering-index lesson turned into a decision framework, not just a syntax choice: every key/INCLUDE column you add helps reads but also widens what every write has to maintain.

    Read Benefit Faster seeks instead of scans Fewer logical reads per query Weighted by how OFTEN the query runs Write Cost Every INSERT/UPDATE/DELETE maintains every index touched Weighted by table’s write frequency

    An index that helps a report run once a week but slows down a table receiving thousands of writes per second is very likely a bad trade — measure both sides, not just the read win.

    Finding Indexes That Aren’t Earning Their Keep

    SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
    s.user_seeks + s.user_scans + s.user_lookups AS total_reads, s.user_updates AS total_writes
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() AND s.user_updates > 0
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC;

    Sort ascending by reads with nonzero writes — the indexes at the top are pure cost, no benefit. Strong drop candidates, pending one more check: confirm they’re not enforcing a UNIQUE constraint or primary key first.

    Key Takeaways

    • Missing index DMVs are hypotheses to verify, not automatic instructions
    • Every index decision is a trade: read benefit vs. write cost, weighted by actual frequency
    • Regularly audit for unused indexes — they cost on every write with zero return

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • SARGability in SQL Server: Why Some WHERE Clauses Can Never Use an Index

    SARGability in SQL Server: Why Some WHERE Clauses Can Never Use an Index

    SARGable (Search ARGument-able) means a predicate is written in a form SQL Server can use to seek an index. Write it wrong, and the index sits there unused — no error, just a silent table scan.

    SARGability, sketched out(seek, or scan? the WHERE clause decides)WHERE YEAR(order_date)=2024function wraps the column —computed for EVERY row→ Index Scanorder_date >= … AND < …column left untouched —range expressed as a boundary→ Index Seeksorted →SEEK — jump straight to the rangeSCAN — checks every single rowComparing VARCHAR to an intliteral? Implicit conversion — samesilent killer, zero errors. 📌

    The Classic Killer: Wrapping the Column

    This directly extends the LIKE-performance aside from Fundamentals Ch.3 — SARGability is the general rule that specific warning was a preview of.

    -- NOT SARGable — the function wraps the column, defeating the index
SELECT * FROM dbo.Orders WHERE YEAR(order_date) = 2024;

-- SARGable — the column itself is untouched, range is expressed instead
SELECT * FROM dbo.Orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

    Why This Happens

    YEAR(order_date) = 2024 Must compute YEAR() for EVERY row before comparing → Index Scan order_date >= ... AND < ... The B-tree can navigate directly to the range → Index Seek

    An index is sorted by the raw column value. The moment you apply a function to the column in the predicate, SQL Server can no longer use that sort order directly — it must evaluate the function per row, which means scanning.

    The Silent Version: Implicit Conversion

    -- Orders.customer_code is VARCHAR(20)
-- NOT SARGable — comparing VARCHAR to an implicit int-to-varchar (or worse) conversion
SELECT * FROM dbo.Orders WHERE customer_code = 12345;  -- literal is int, column is varchar

-- SARGable — matching types
SELECT * FROM dbo.Orders WHERE customer_code = '12345';

    This one is genuinely dangerous because it produces no error and often no obvious symptom in small tests — only under real data volume does the scan become visible. Data type mismatches between application code and column definitions are a very common, very silent SARGability killer.

    Other Common Non-SARGable Patterns

    • WHERE column LIKE '%something' — a leading wildcard can't seek (trailing wildcard 'something%' still can)
    • WHERE column + 1 = 100 — arithmetic on the column instead of the constant
    • WHERE ISNULL(column, '') = 'value' — wraps the column in a function again

    Enjoyed this?

    Subscribe to get every new lesson as soon as it's published, and share it with a developer who'd find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Columnstore Indexes in SQL Server: When to Use Them for Analytical Workloads

    Columnstore Indexes in SQL Server: When to Use Them for Analytical Workloads

    Everything covered so far is a rowstore B-tree. Columnstore indexes are a fundamentally different storage model — and a fundamentally different use case.

    Rowstore vs Columnstore, sketched out(same data, two different layouts)Rowstorereads a full ROW at onceColumnstorereads a full COLUMN at onceOLTP: “fetch order #4471”one row → rowstore winsOLAP: “SUM(amount) by month”millions of rows → columnstore winsFrequent single-row updates on acolumnstore table? Bad fit — reserveit for fact & reporting tables. 📌

    Row Storage vs Column Storage

    Rowstore Stores a full row together Great for: fetching one/few rows (OLTP: “get this order”) Columnstore Stores each column together, heavily compressed Great for: scanning/aggregating millions of rows (OLAP: “sum revenue by month”)

    Creating One

    CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON dbo.SalesFact (product_id, region_id, sale_date, amount);

-- Or make the whole table columnstore-organized (common for pure fact tables)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesFact ON dbo.SalesFact;

    Why It’s Fast: Compression and Batch Mode

    Columnar storage compresses extremely well (repeated values in a single column compress far better than mixed row data), and queries against columnstore indexes execute in batch mode — processing ~900 rows at a time per operator call instead of one row at a time, dramatically cutting CPU overhead for large aggregations.

    When NOT to Use Columnstore

    This is the practical, real-world caveat behind why rowstore vs. columnstore isn’t a strict upgrade — it’s a workload match, exactly like choosing an iTVF vs. mTVF in the Developers & DBAs course came down to matching the tool to the shape of the problem.

    Columnstore is a poor fit for OLTP-style point lookups and frequent single-row updates — it’s optimized for bulk scan/aggregate patterns, not “fetch order #4471.” Using it as your primary OLTP table index is a common, costly mistake. Reserve it for fact tables, reporting tables, and genuinely analytical workloads.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • SQL Server Join Algorithms: Nested Loop, Hash Match, and Merge Join Explained

    SQL Server Join Algorithms: Nested Loop, Hash Match, and Merge Join Explained

    Every JOIN you write compiles down to one of three physical algorithms. The optimizer picks based on table sizes, available indexes, and sort order — not the JOIN keyword you typed.

    Join Algorithms, sketched out(the optimizer picks — not your JOIN keyword)Nested Loopoutersmall sideseek indexper rowrepeat × each outer rowO(N × seek cost)Hash Matchbuild (small)probe (large)hash tablespills to tempdb if too bigO(N + M), memory-hungryMerge Joinwalk both sorted inputs, onceO(N + M), needs sort orderfree when sort comes from a clustered index

    The Three Algorithms

    Algorithm Best when Cost profile
    Nested Loop One side is small, other side is indexed on the join key Cheap for small × large with a good index; terrible for large × large
    Hash Match Both sides large, no useful sort order Builds an in-memory hash table on the smaller side; can spill to tempdb if too big
    Merge Join Both sides already sorted on the join key Very cheap when sort order is free (e.g. from a clustered index)

    Visualizing the Decision

    Nested Loop For each outer row, seek the inner index O(N × seek cost) Hash Match Build hash table on smaller input, probe with larger O(N + M), memory-hungry Merge Join Walk both sorted inputs in lockstep, once O(N + M), needs sort order

    Reading It in the Plan

    This is exactly the Chapter 3 execution-plan-reading skill (Developers & DBAs course) applied to a new operator family — the same right-to-left reading order, now watching for which JOIN algorithm appears rather than Seek vs Scan.

    When you see Hash Match on a query you expected to be a quick lookup, that’s a strong signal one side of the join is much larger than expected, or a useful index is missing. When you see Nested Loop with a huge outer row count, that’s the opposite problem — an algorithm suited for small inputs being forced onto a large one.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Cardinality Estimation and Parameter Sniffing in SQL Server Explained

    Cardinality Estimation and Parameter Sniffing in SQL Server Explained

    Every plan the optimizer builds rests on a guess: how many rows will this operator produce? When that guess is wrong, the whole plan can be wrong — and parameter sniffing is the single most common way it happens.

    Parameter Sniffing, sketched out(same cached plan, very different data)ShippedDeliveredCancelledReturnedPending① compiled: 50 rows③ reused: 3,000,000 rows① First call: ‘cancelled’50 rows estimated — Seek plancompiled AND cachedplan cache② Second call: ‘pending’3,000,000 rows — SAME seek planreused — catastrophically slowDiagnose with WITH RECOMPILE.Fix via OPTION(RECOMPILE) or splitprocs — trades CPU vs stability. 📌

    Cardinality Estimation, in One Sentence

    The Cardinality Estimator uses statistics (histograms on indexed/queried columns) to predict row counts at each step of a plan, and picks join algorithms, index usage, and memory grants based on those predictions — not the real data, which it hasn’t seen yet.

    Parameter Sniffing: A Concrete Example

    This is the exact mechanism previewed back in the Developers & DBAs course’s stored-procedure lesson — the precompilation that makes procedures fast is the same precompilation that causes this.

    CREATE PROCEDURE dbo.usp_GetOrdersByStatus @status NVARCHAR(20)
AS
SELECT * FROM dbo.OrderLog WHERE order_status = @status;

-- First call: 'cancelled' matches 50 rows out of 5 million — compiles a Seek-based plan
EXEC dbo.usp_GetOrdersByStatus @status = 'cancelled';

-- Second call: 'pending' matches 3 million rows — REUSES the seek-based plan, now terrible
EXEC dbo.usp_GetOrdersByStatus @status = 'pending';

    What’s Actually Happening

    First compile: ‘cancelled’ Estimated: 50 rows Plan: Index Seek (correct fit) Plan cached for this proc Reused for: ‘pending’ Actual: 3,000,000 rows Still uses the seek plan — catastrophically slow

    Diagnosing It

    -- Compare estimated vs actual rows in the plan (Ctrl+M in SSMS)
-- A huge gap on a parameterized proc call is the signature of parameter sniffing

-- Force a fresh compile per call to test the theory (not a permanent fix, a diagnostic)
EXEC dbo.usp_GetOrdersByStatus @status = 'pending' WITH RECOMPILE;

    If the RECOMPILE version is fast, you’ve confirmed parameter sniffing. Real fixes include OPTION (RECOMPILE) on the specific statement, query hints, or splitting into separate procedures for genuinely different data distributions — each with real trade-offs in CPU cost vs plan stability.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • Case Study: The 13-Hour Delete — A Real SQL Server Performance Diagnosis Walkthrough

    Case Study: The 13-Hour Delete — A Real SQL Server Performance Diagnosis Walkthrough

    The problem: a nightly cleanup job deletes old records from a large partitioned table. It used to take 20 minutes. This week it took 13 hours, and blocked other processes the entire time. Let’s diagnose it exactly the way you would in production — using the Evidence-First workflow from the start of this course.

    The 13-Hour Delete, sketched out(evidence-first, applied to a real incident)1. Baselinejob normally takes20 minutes2. CaptureEvidenceXE: PAGEIOLATCH waits3. DiscoveryFK column hasNO index4. The FixONE nonclusteredindex, ONLINE=ON5. Validate22 minutes,Index Seek nowPrimary Keyautomatically indexedby SQL ServervsForeign KeyNOT automatically indexed— easy to overlook400M rows + full scan on the FK = 13 hours blocked

    Step 1 — Baseline

    -- What did "normal" look like? Check historical job duration logs first.
-- Then measure the current run's I/O and duration directly:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- (run a scoped-down version of the delete against a copy/test environment)

    Without a baseline, “13 hours” is just a scary number — you need “20 minutes normally” to even know how far off this is, and to confirm later that a fix actually worked.

    Step 2 — Capture Evidence

    CREATE EVENT SESSION DeleteDiagnosis ON SERVER
ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.sql_text))
ADD EVENT sqlos.wait_info (WHERE wait_type LIKE 'PAGEIOLATCH%' OR wait_type LIKE 'LCK%')
ADD TARGET package0.event_file (SET filename = N'DeleteDiagnosis');
GO
ALTER EVENT SESSION DeleteDiagnosis ON SERVER STATE = START;
-- Let the job run, then inspect captured wait types

    The captured evidence shows overwhelming PAGEIOLATCH_SH waits — the query is spending almost all its time waiting to read data pages from disk, not on CPU or locks.

    Step 3 — Discovery

    -- Capture the actual execution plan for the DELETE's WHERE clause
SELECT * FROM dbo.LargeAuditTable
WHERE customer_id IN (SELECT customer_id FROM dbo.DeactivatedCustomer);
-- Plan shows: Clustered Index Scan on LargeAuditTable — no usable index on customer_id (a foreign key with no supporting index)

    The root cause: customer_id is a foreign key on a 400-million-row table with no index. Every batch of the delete performs a full clustered index scan to find matching rows — exactly the missing-index-on-a-foreign-key pattern that’s easy to overlook because foreign keys don’t automatically get indexed in SQL Server (unlike primary keys).

    Step 4 — The Fix

    CREATE NONCLUSTERED INDEX IX_LargeAuditTable_CustomerId
ON dbo.LargeAuditTable (customer_id)
WITH (ONLINE = ON, MAXDOP = 4);  -- ONLINE to avoid blocking production during creation

    One targeted index — not a rewrite of the whole delete process, not a hardware upgrade. This is the discipline from the Evidence-First loop: change one variable at a time.

    Step 5 — Validation

    -- Re-run the same scoped test, compare against baseline
SET STATISTICS IO ON;
-- Expect: Index Seek instead of Clustered Index Scan, dramatically fewer logical reads

    The rerun completes in 22 minutes — back in line with the historical baseline, with the execution plan now showing an Index Seek instead of a full scan.

    The Lesson, Beyond This One Incident

    Notice how this walkthrough used every earlier lesson in this module: the missing foreign-key index is what Ch.109’s evidence-based indexing lesson calls a hypothesis to verify; the clustered index scan is exactly the SARGability/plan-reading pattern from this module’s first lesson; and the whole five-step shape is the Evidence-First workflow from the very start of this course, applied for real.

    Foreign keys don’t get an index automatically in SQL Server — unlike the primary key side of the relationship. Any DELETE, UPDATE, or JOIN filtering on a foreign key column without a supporting index is a latent “13-hour delete” waiting for the table to grow large enough to matter.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.

  • SQL Server Execution Plan Operators: Costing, Plan Shape, and What to Ignore

    SQL Server Execution Plan Operators: Costing, Plan Shape, and What to Ignore

    You’ve seen individual operators already (Seek, Scan, Key Lookup). Now let’s read a whole plan the way someone diagnosing a real incident actually does.

    Execution Plans, sketched out(cost % is an estimate — verify against actual rows)Index SeekCustomer.PKcost: 2%50 rowsKey LookupOrderLog clustered indexcost: 91%!480,000 rows!Nested LoopJoin (root)cost: 5%The gap that actually matters:Estimated: 50 rowspredicted when the plan compiled(shown in the plan, not measured)vsActual: 480,000 rowswhat really came back9,600x gap — the real signalHigh cost % is a fine place to start —but it’s estimate-based. Check actualrows before trusting it. 📌

    Cost Percentages: Useful, But Not the Whole Story

    Cost % is an ESTIMATE-based number, computed pre-execution it can be badly wrong when estimates are wrong (see Module 3)

    The operator showing “87% cost” is a reasonable place to start looking — but it’s computed from the optimizer’s row estimates, which you now know can be badly wrong under parameter sniffing or stale statistics. Cross-check cost % against actual row counts, not just at face value.

    Reading Plan Shape, Not Just Individual Icons

    • Thick arrows between operators represent many rows flowing — trace these back to find where row counts balloon unexpectedly
    • Warning icons (yellow triangle) flag things like implicit conversions or missing statistics directly in the plan — don’t skip past these
    • Parallelism icons (yellow circle with arrows) show where the plan split across threads — useful context, not automatically good or bad

    Estimated vs Actual: The Signal That Matters Most

    -- Always use ACTUAL execution plan (Ctrl+M), not estimated — estimated never shows real row counts
SELECT * FROM dbo.OrderLog WHERE customer_id = 42;

    Hover any operator and compare Estimated Number of Rows to Actual Number of Rows. A 10x+ gap anywhere in the plan is the single strongest signal something upstream (stale stats, a non-SARGable predicate, parameter sniffing) is misleading the optimizer — often more informative than the cost percentage itself.

    Enjoyed this?

    Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.

    📡 Subscribe via RSS  | 
    Share on X  | 
    Share on LinkedIn  | 
    Share on Facebook

    Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.