https://sqlmaster.mssqlplanner.com

Tag: Query Optimization

  • 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.

  • 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.

  • Module 3 Exercises: SQL Server Query Optimization Labs (10 Hands-On Exercises)

    Module 3 Exercises: SQL Server Query Optimization Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs.

    Guided Labs

    Guided1. Create a table with an indexed date column, then compare the plan for YEAR(col)=2024 vs a SARGable range predicate.

    SELECT * FROM dbo.OrderLog WHERE YEAR(order_date) = 2024; -- capture plan
SELECT * FROM dbo.OrderLog WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'; -- capture plan, compare
    Guided2. Reproduce an implicit conversion scan: compare a VARCHAR column filtered with an unquoted numeric literal vs a quoted string literal.
    Guided3. Join a small lookup table to a large table and identify the join algorithm chosen in the plan.
    Guided4. Force a Hash Match with OPTION (HASH JOIN) and a Nested Loop with OPTION (LOOP JOIN) on the same query, and compare logical reads via STATISTICS IO.

    SELECT * FROM dbo.A JOIN dbo.B ON A.id = B.a_id OPTION (HASH JOIN);
SELECT * FROM dbo.A JOIN dbo.B ON A.id = B.a_id OPTION (LOOP JOIN);
    Guided5. Reproduce parameter sniffing: create a procedure filtering on a skewed column, call it first with a rare value then a common one, and compare plans.

    Challenge Scenarios

    Challenge6. A reporting query with a LIKE '%searchterm%' predicate is scanning a 10-million-row table. Propose two different approaches to make it faster, with trade-offs for each.
    Challenge7. A stored procedure is fast for 95% of callers but catastrophically slow for a specific customer_id. Walk through your diagnostic process using this module's tools.
    Challenge8. Given a database backup and a "slow query report" showing a DELETE with an unindexed foreign key filter, write the full incident diagnosis following the 13-Hour Delete's five-step structure.

    Break-It Labs

    Break-It9. Deliberately create a non-SARGable predicate on a large table (wrap an indexed column in ISNULL()), measure the resulting scan cost with STATISTICS IO, then rewrite it SARGably and re-measure.
    Break-It10. Deliberately induce parameter sniffing pain: create a procedure on a heavily skewed column, force a bad plan to cache via a rare-value first call, then fix it with OPTION (RECOMPILE) and measure the difference for the common-value call.

    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.

  • Module 3 Quiz: SQL Server Query Optimization (10 Questions)

    Module 3 Quiz: SQL Server Query Optimization

    10 questions covering SARGability, join algorithms, cardinality estimation, and the 13-Hour Delete case study.

    1. Why does `WHERE YEAR(order_date) = 2024` defeat an index on order_date?

    A) YEAR() is not a valid function
    B) Wrapping the column in a function means SQL Server must evaluate it per row instead of seeking the sorted index
    C) 2024 is not a valid year
    D) It doesn’t defeat the index

    Show Answer

    Answer: B

    The index is sorted by the raw column; applying a function breaks that direct usability, forcing a scan.

    2. What’s the SARGable rewrite of `WHERE YEAR(order_date) = 2024`?

    A) There is no rewrite possible
    B) `WHERE order_date >= ‘2024-01-01’ AND order_date < '2025-01-01'`
    C) `WHERE order_date = 2024`
    D) `WHERE CAST(order_date AS INT) = 2024`

    Show Answer

    Answer: B

    Expressing it as a range on the untouched column lets the optimizer seek directly.

    3. Why is an implicit data type conversion (e.g. comparing a VARCHAR column to an INT literal) a dangerous SARGability killer?

    A) It throws a clear error immediately
    B) It silently defeats the index with no error, often invisible until real data volume
    C) It’s actually faster
    D) SQL Server doesn’t allow this

    Show Answer

    Answer: B

    No error, no warning — just a scan that only becomes visibly slow once the table is large. This is exactly the “gotcha” the course outline calls out.

    4. When does the optimizer typically favor a Nested Loop join?

    A) When both tables are enormous
    B) When one side is small and the other is indexed on the join key
    C) Only for UPDATE statements
    D) Never, it’s deprecated

    Show Answer

    Answer: B

    Nested Loop is cheap for small × large-with-index, but scales poorly for large × large without one.

    5. What does seeing a Hash Match on a query you expected to be a quick lookup usually signal?

    A) Everything is fine
    B) One side of the join is larger than expected, or a useful index is missing
    C) The query is guaranteed to be fast
    D) The database is corrupted

    Show Answer

    Answer: B

    Hash Match appears when both sides are large with no useful sort order — unexpected on a query you thought was selective.

    6. What does the Cardinality Estimator use to predict row counts at each plan step?

    A) The actual data, read in advance
    B) Statistics/histograms, not the real data it hasn’t seen yet
    C) A random number generator
    D) The table’s name

    Show Answer

    Answer: B

    Estimates are predictions based on statistics — which is exactly why stale statistics (Module 2) directly cause bad cardinality estimates.

    7. What is parameter sniffing?

    A) A security vulnerability
    B) A cached plan compiled for one parameter value being reused for a very differently-shaped later call
    C) A type of index
    D) Encrypting stored procedure parameters

    Show Answer

    Answer: B

    The plan cache reuses the first-compiled plan; if later calls have very different data distribution, that plan can be badly wrong.

    8. What’s a fast diagnostic (not permanent fix) to confirm parameter sniffing is the cause of a slow procedure call?

    A) Restart the server
    B) Run the call with WITH RECOMPILE and compare
    C) Rebuild all indexes
    D) There’s no way to confirm it

    Show Answer

    Answer: B

    If forcing a fresh compile for that specific parameter value fixes performance, you’ve confirmed the cached plan was the problem.

    9. In the 13-Hour Delete case study, what wait type dominated the captured evidence?

    A) LCK_M_X (lock waits)
    B) PAGEIOLATCH_SH (waiting to read data pages from disk)
    C) CXPACKET (parallelism waits)
    D) No waits were present

    Show Answer

    Answer: B

    Overwhelming PAGEIOLATCH_SH waits pointed to disk I/O from a full scan — not locking, not CPU — which directed the investigation toward the missing index.

    10. Why did the 13-Hour Delete’s root cause (a missing index on a foreign key) go unnoticed for so long?

    A) SQL Server automatically indexes foreign keys, so this couldn’t happen
    B) Foreign keys are NOT automatically indexed in SQL Server, unlike primary keys
    C) The table was too small to matter
    D) It was actually a hardware failure

    Show Answer

    Answer: B

    Unlike the primary key side, SQL Server never auto-creates an index on a foreign key column — a very common latent performance trap on tables that grow over time.

    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.