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

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);

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