https://sqlmaster.mssqlplanner.com

Author: admin

  • Module 5 Exercises: SQL Server Locking & Concurrency Labs (10 Hands-On Exercises)

    Module 5 Exercises: SQL Server Locking & Concurrency Labs

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

    Guided Labs

    Guided1. Pull the most recent deadlock graph from system_health on your instance (there may be none if the instance is quiet — that’s a valid result too).
    Guided2. Query sys.dm_os_waiting_tasks and sys.dm_os_wait_stats, and classify all current wait types as LCK, LATCH, or neither.
    Guided3. Query sys.dm_db_file_space_usage for tempdb and check how many data files currently exist.

    SELECT * FROM tempdb.sys.database_files WHERE type = 0;
    Guided4. In a test/dev instance only, add a second equally-sized tempdb data file and confirm both are the same size.
    Guided5. Open two SSMS query windows and manually reproduce a simple deadlock (opposite update order on two rows), then pull the resulting graph from system_health.

    Challenge Scenarios

    Challenge6. A support ticket says “the database is locking up” during a bulk insert job. Using this module’s DMVs, determine whether this is lock contention, latch contention, or something else entirely.
    Challenge7. A high-throughput OLTP table using an ever-increasing IDENTITY key shows growing PAGELATCH_EX waits as load increases. Propose two different structural fixes and their trade-offs.
    Challenge8. A server shows heavy tempdb PAGELATCH contention but already has 4 tempdb files of visibly different sizes. Diagnose why the existing files aren’t helping.

    Break-It Labs

    Break-It9. In a disposable test database, deliberately create a table-scan-vs-targeted-update deadlock (not the classic two-row-swap kind) and capture its graph.
    Break-It10. Deliberately induce tempdb contention: run many concurrent sessions each creating/dropping local temp tables in a tight loop against a single-file tempdb, observe PAGELATCH waits climb, then add tempdb files and re-measure.

    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 4 Exercises: SQL Server Execution Plan Labs (10 Hands-On Exercises)

    Module 4 Exercises: SQL Server Execution Plan Labs

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

    Guided Labs

    Guided1. Capture an actual execution plan and note the Estimated vs Actual rows for every operator.
    Guided2. Get the raw XML for a plan using SET SHOWPLAN_XML ON and locate the root RelOp element.
    Guided3. Search a captured plan’s XML for the string “Warning” and interpret any results found.
    Guided4. Query sys.dm_exec_cached_plans for the 10 plans with the highest total_worker_time (CPU) currently cached.

    SELECT TOP 10 qs.total_worker_time, st.text
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
    Guided5. Force a sort spill by running an ORDER BY on a wide result set with a deliberately restricted memory grant (OPTION (MIN_GRANT_PERCENT/MAX_GRANT_PERCENT) if available, or a genuinely large sort), then find “SpillToTempDb” in the plan XML.

    Challenge Scenarios

    Challenge6. Given a 25-operator plan with no single operator above 20% cost, describe your approach to finding the real bottleneck (hint: it’s not always the highest-cost single operator).
    Challenge7. A plan shows a Nested Loop with an outer row estimate of 10 but an actual of 2 million. Explain what this means and what you’d check next.
    Challenge8. Write a query against sys.dm_exec_cached_plans that finds every currently-cached plan containing an accidental CROSS JOIN signature.

    Break-It Labs

    Break-It9. Deliberately write a query with a missing join condition (accidental CROSS JOIN) on two mid-sized tables, capture the plan, and confirm “NoJoinPredicate” appears in the XML.
    Break-It10. Deliberately create a huge Estimated-vs-Actual gap: use OPTION (RECOMPILE) with a deliberately wrong local variable technique to defeat estimation, capture the resulting plan, then fix it and compare.

    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 2 Exercises: SQL Server Indexing Strategy Labs (10 Hands-On Exercises)

    Module 2 Exercises: SQL Server Indexing Strategy Labs

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

    Guided Labs

    Guided1. Query sys.dm_db_missing_index_details and rank results by avg_user_impact × user_seeks.

    SELECT d.statement, d.equality_columns, d.included_columns, s.avg_user_impact, s.user_seeks
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;
    Guided2. Find every index in a test database with zero reads but nonzero writes.

    SELECT OBJECT_NAME(s.object_id) AS table_name, i.name
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.user_seeks=0 AND s.user_scans=0 AND s.user_lookups=0 AND s.user_updates>0;
    Guided3. Measure fragmentation on all indexes in a test database and sort descending.

    SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name, ips.avg_fragmentation_in_percent
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
ORDER BY ips.avg_fragmentation_in_percent DESC;
    Guided4. Check statistics staleness on a table you’ve recently bulk-loaded into.

    SELECT s.name, sp.last_updated, sp.modification_counter
FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('YourTableName');
    Guided5. Create a clustered columnstore index on a wide, append-heavy “fact-style” test table and compare SELECT SUM(…) query duration before and after.

    Challenge Scenarios

    Challenge6. A table has 12 nonclustered indexes and insert performance has degraded badly over a year. Using this module’s DMVs, design a plan to identify which indexes are safe to drop.
    Challenge7. A weekly maintenance job rebuilds every index regardless of fragmentation, taking 6 hours and causing blocking. Redesign the job using the evidence-first thresholds from this module.
    Challenge8. A reporting query aggregating 50 million rows for a monthly dashboard takes 40 minutes on a rowstore table. Propose a columnstore-based redesign and explain why it would help, referencing batch mode.

    Break-It Labs

    Break-It9. Deliberately create heavy fragmentation: insert 50,000 rows in random GUID order into a table with a GUID clustered key, measure fragmentation, then fix it with REBUILD and re-measure.
    Break-It10. Deliberately induce a bad plan from stale statistics: disable auto-update statistics on a test table, bulk-load 10x its original row count, run a previously-fast query and observe the degraded plan, then manually UPDATE STATISTICS and confirm the plan improves.

    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 7 Quiz: SQL Server Advanced Performance Features (10 Questions)

    Module 7 Quiz: SQL Server Advanced Performance Features

    1. What problem from earlier in this course does In-Memory OLTP most directly address?

    A) Missing indexes
    B) Lock and latch contention under extreme concurrency (Module 5)
    C) Stale statistics
    D) SARGability

    Show Answer

    Answer: B

    Memory-optimized tables use row-versioning and avoid traditional page structures, eliminating the lock/latch mechanisms entirely for those tables.

    2. What makes a natively compiled stored procedure different from a regular one?

    A) No difference at all
    B) It compiles to actual machine code, with a restricted T-SQL surface area
    C) It runs slower but is more flexible
    D) It can only be called from the application tier

    Show Answer

    Answer: B

    The performance ceiling is much higher, but you give up dynamic SQL and other T-SQL features to get it.

    3. Is In-Memory OLTP a good default upgrade for a general-purpose reporting table?

    A) Yes, always upgrade everything
    B) No — it’s best reserved for confirmed extreme-contention scenarios, not a default choice
    C) It’s required for all tables in modern SQL Server
    D) Reporting tables can’t be disk-based

    Show Answer

    Answer: B

    Reach for it only after evidence confirms lock/latch contention is the actual bottleneck — the Evidence-First principle applies here too.

    4. What does a Resource Governor resource pool control?

    A) Disk space quotas only
    B) A capped slice of CPU and memory for a given workload group
    C) User passwords
    D) Backup schedules

    Show Answer

    Answer: B

    Resource pools cap CPU/memory consumption, preventing one workload from starving another sharing the same instance.

    5. What routes an incoming connection to a specific Resource Governor workload group?

    A) The connection string alone
    B) A classifier function evaluated at login
    C) Random assignment
    D) The database name only

    Show Answer

    Answer: B

    The classifier function (e.g. checking SUSER_SNAME()) decides which workload group governs each new session.

    6. Does Resource Governor fix a badly-written, missing-index query?

    A) Yes, it automatically optimizes queries
    B) No — it limits blast radius (CPU/memory cap), it doesn’t fix the query itself
    C) It rewrites the query automatically
    D) It adds missing indexes automatically

    Show Answer

    Answer: B

    It’s a containment guarantee, not a substitute for the actual tuning work from Modules 2-4.

    7. What does enabling memory-optimized tempdb metadata fix, and how does it differ from just adding more tempdb files?

    A) It has the same effect as adding files
    B) It moves tempdb’s system metadata into lock/latch-free structures, eliminating contention at the source rather than spreading it across files
    C) It disables tempdb entirely
    D) It only works on Azure

    Show Answer

    Answer: B

    Multiple files (Module 5) dilute contention; memory-optimized metadata removes the underlying lock/latch mechanism for tempdb system tables entirely.

    8. Is SQL Server Profiler available on Azure SQL Database?

    A) Yes, identical to on-prem
    B) No — Extended Events is the only option there
    C) Only on weekends
    D) Profiler is required, not optional, on Azure

    Show Answer

    Answer: B

    This is exactly why Module 6 taught Extended Events thoroughly — it’s the portable skill across on-prem and Azure SQL Database.

    9. Is Resource Governor available on Azure SQL Database (single/elastic pool)?

    A) Yes, fully available
    B) No — workload isolation instead comes from service tier/compute choice itself
    C) Only for Enterprise Edition
    D) It’s the default behavior with no configuration needed

    Show Answer

    Answer: B

    On Azure SQL Database, the platform abstracts this — your service tier/vCore choice is the isolation mechanism instead.

    10. Do the Evidence-First diagnostic principles from this entire course still apply on Azure SQL Database?

    A) No, Azure requires an entirely different approach
    B) Yes — the diagnostic principles are identical; only which infrastructure-level levers are directly available changes
    C) Only Query Store works on Azure
    D) Azure SQL doesn’t support DMVs

    Show Answer

    Answer: B

    SARGability, execution plans, DMVs, and Query Store all apply the same way — Azure just abstracts some infrastructure control (Resource Governor, tempdb files, Profiler).

    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 1 Exercises: SQL Server Architecture Labs (10 Hands-On Exercises)

    Module 1 Exercises: SQL Server Architecture Labs

    5 guided labs, 3 challenge scenarios, and 2 break-it labs — applying the Evidence-First workflow to real architecture questions.

    Guided Labs (step-by-step)

    Guided1. Measure your buffer pool’s current cached size and compare it to total server RAM.

    SELECT COUNT(*) * 8 / 1024 AS cached_data_mb FROM sys.dm_os_buffer_descriptors;
SELECT total_physical_memory_kb/1024 AS total_ram_mb FROM sys.dm_os_sys_memory;
    Guided2. Find the top 5 largest cached execution plans by size and note their objtype.

    SELECT TOP 5 objtype, size_in_bytes/1024 AS size_kb, usecounts
FROM sys.dm_exec_cached_plans ORDER BY size_in_bytes DESC;
    Guided3. Check your current transaction log size and percent used for a test database.

    DBCC SQLPERF(LOGSPACE);
    Guided4. Create a table, insert 10,000 rows, then check how many pages it occupies.

    CREATE TABLE dbo.PageDemo (id INT IDENTITY PRIMARY KEY, filler CHAR(500));
INSERT INTO dbo.PageDemo (filler)
SELECT TOP 10000 REPLICATE('x',500) FROM sys.all_objects a CROSS JOIN sys.all_objects b;
SELECT page_count, record_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PageDemo'), NULL, NULL, 'DETAILED');
    Guided5. Run the same parameterized query twice with very different parameter selectivity and compare the cached plan’s row estimates to actual. Use Ctrl+M in SSMS to capture both actual execution plans and note if the estimate matches the second call.

    Challenge Scenarios (diagnose independently)

    Challenge6. A colleague reports the server has 64GB RAM but query performance hasn’t improved since a recent upgrade from 16GB. Using the DMVs from this module, form a hypothesis for why RAM alone didn’t help, and what evidence you’d gather next.
    Challenge7. A transaction log grew from 1GB to 50GB overnight with no obvious large operations in the app. List three architectural causes you’d investigate first, and the exact DMV/DBCC command for each.
    Challenge8. The plan cache shows thousands of single-use ad-hoc plans consuming significant memory. Explain the architectural reason this happens and one setting that mitigates it.

    Break-It Labs

    Break-It9. Deliberately induce plan cache bloat: run 500 slightly-different ad-hoc (non-parameterized) queries in a loop, then measure plan cache growth with the query from Guided Lab 2. Then fix it by enabling “optimize for ad hoc workloads” and re-measure.
    Break-It10. Deliberately induce log growth pressure: run a large batch DELETE inside an explicit transaction without committing for several minutes while inserting more rows elsewhere, then observe DBCC SQLPERF(LOGSPACE) growing. Resolve it by committing and note the log space reclaimed (or not, until a log backup, depending on recovery model).

    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 6 Quiz: SQL Server Monitoring & Tooling (10 Questions)

    Module 6 Quiz: SQL Server Monitoring & Tooling

    1. How does Query Store differ from the plan cache?

    A) They are identical
    B) Query Store persists history per-database across restarts; the plan cache resets on restart
    C) Query Store only works on Azure
    D) The plan cache is more detailed

    Show Answer

    Answer: B

    This persistence is exactly what makes Query Store useful for tracking regression over time, not just current state.

    2. What does sp_query_store_force_plan let you do?

    A) Delete a query from history
    B) Pin a specific known-good plan for a query, without changing code
    C) Force a full index rebuild
    D) Disable Query Store

    Show Answer

    Answer: B

    This is a direct, reversible, production-safe response to parameter sniffing regressions.

    3. Why should a production Extended Events session use an aggressive WHERE duration filter?

    A) It’s not necessary
    B) To capture only what actually matters and keep overhead/disk usage low
    C) Filters are required by SQL Server syntax
    D) It has no effect on overhead

    Show Answer

    Answer: B

    Unfiltered capture on a busy server generates enormous volume — filtering aggressively is the difference between diagnostic and production-safe.

    4. What does EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS accomplish?

    A) Guarantees zero events are ever lost
    B) Prioritizes server performance over perfect event capture completeness
    C) Disables the session entirely
    D) Doubles the memory buffer

    Show Answer

    Answer: B

    This ensures monitoring itself never becomes a bottleneck — an intentional trade-off for production sessions.

    5. What does a low Page Life Expectancy counter typically indicate?

    A) Excellent buffer pool health
    B) Buffer pool pressure — pages being evicted and re-read from disk quickly
    C) A CPU bottleneck only
    D) A network issue

    Show Answer

    Answer: B

    This ties directly back to Module 1’s buffer pool concept — low PLE means data isn’t staying cached.

    6. A rising Compilations/sec relative to Batch Requests/sec often signals what?

    A) Excellent plan reuse
    B) Excessive recompiling, often from ad-hoc (non-parameterized) query bloat
    C) A hardware failure
    D) Normal, healthy behavior always

    Show Answer

    Answer: B

    This connects back to Module 1’s plan cache bloat discussion — too many unique ad-hoc statements compiling fresh plans.

    7. A rising Full Scans/sec trend alongside a stable workload most likely points to what?

    A) Everything is fine
    B) Missing or degraded indexes (fragmentation, stale stats)
    C) A network slowdown
    D) Increased RAM

    Show Answer

    Answer: B

    A trend change with stable workload is a strong signal something structural (Module 2 territory) has degraded.

    8. Which tool is best suited for answering “how has this specific query’s performance evolved over the past month”?

    A) sys.dm_os_waiting_tasks (live-only)
    B) Query Store
    C) PerfMon alone
    D) The plan cache alone

    Show Answer

    Answer: B

    Query Store’s whole design purpose is persisted, per-query historical tracking — exactly this question shape.

    9. Which tool is best for answering “what is currently blocking session 55, right now”?

    A) Query Store
    B) sys.dm_os_waiting_tasks
    C) PerfMon historical logs
    D) sys.dm_exec_query_stats

    Show Answer

    Answer: B

    This is a live, real-time question — the DMV showing current wait state is the right tool, not a historical aggregate.

    10. Why is combining multiple monitoring tools (not relying on just one) the recommended approach?

    A) It’s unnecessary, one tool does everything
    B) Each tool answers a different question shape — trend over time, current state, specific events, or per-query history
    C) More tools always means more accuracy regardless of fit
    D) This is not actually recommended

    Show Answer

    Answer: B

    PerfMon (trend), DMVs (current/historical state), XEvents (specific captured events), Query Store (per-query history) are complementary, not redundant.

    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 5 Quiz: SQL Server Locking & Concurrency (10 Questions)

    Module 5 Quiz: SQL Server Locking & Concurrency

    1. Which SQL Server session runs by default and continuously captures deadlock graphs?

    A) default_trace
    B) system_health
    C) AlwaysOn_health
    D) None run by default

    Show Answer

    Answer: B

    system_health is an always-on Extended Events session that captures deadlock graphs (and more) without any setup, retrievable after the fact via its ring buffer target.

    2. In a deadlock graph’s XML, what does the victim-list element tell you?

    A) Nothing useful
    B) Which process SQL Server chose to kill to break the deadlock
    C) The server’s IP address
    D) The backup schedule

    Show Answer

    Answer: B

    Cross-referencing the victim against the survivor’s SQL text is how you confirm the actual access-order conflict.

    3. What do locks primarily protect?

    A) Physical memory pages
    B) Logical data consistency across a transaction
    C) Network packets
    D) CPU scheduling

    Show Answer

    Answer: B

    Locks are held for the duration of the transaction to protect logical consistency (isolation).

    4. What do latches primarily protect, and for how long are they typically held?

    A) Logical data, for the whole transaction
    B) Physical in-memory structures, typically for microseconds
    C) Network connections, indefinitely
    D) User permissions

    Show Answer

    Answer: B

    Latches are a much shorter-duration, lower-level mechanism protecting physical page access, not transactional consistency.

    5. “Gotcha”: A DBA sees high PAGELATCH_EX waits and shortens application transactions to fix it. Why is this the wrong fix?

    A) Shortening transactions always fixes everything
    B) Latch contention is about physical page access, not transaction duration — a different problem needing a different fix
    C) PAGELATCH_EX doesn’t exist
    D) This is actually the correct fix

    Show Answer

    Answer: B

    Confusing latch contention for lock contention leads to solving the wrong problem — the lock-blocking playbook doesn’t address physical page contention.

    6. What’s the classic latch contention pattern on a table with an ever-increasing IDENTITY key under heavy concurrent inserts?

    A) No contention is possible
    B) All sessions race to insert into the same last physical page, causing PAGELATCH_EX waits
    C) It only affects SELECT queries
    D) It causes data corruption automatically

    Show Answer

    Answer: B

    Ever-increasing keys concentrate all inserts on the same “hot” last page — a well-known latch contention scenario.

    7. What do GAM, SGAM, and PFS pages track in tempdb?

    A) User permissions
    B) Space allocation — which pages/extents are free or in use
    C) Query execution plans
    D) Backup history

    Show Answer

    Answer: B

    These are allocation-tracking pages every session touches when claiming space for temp tables/table variables.

    8. What’s the standard fix for tempdb allocation page contention?

    A) Reduce tempdb to a single file
    B) Multiple equally-sized tempdb data files, spreading round-robin allocation
    C) Disable tempdb entirely
    D) Increase the transaction log size only

    Show Answer

    Answer: B

    Multiple files spread contention across separate allocation page sets — a well-established, standard configuration recommendation.

    9. “Gotcha”: Why might adding tempdb data files of unequal size fail to help?

    A) Unequal sizes are always fine
    B) Proportional-fill allocation favors the file with the most free space, defeating round-robin distribution
    C) SQL Server ignores extra files
    D) It always improves performance regardless of size

    Show Answer

    Answer: B

    Equal file sizes are essential for the round-robin allocation benefit to actually spread load evenly.

    10. Which DMV/view shows what a currently-waiting session is actually waiting on, including its resource_description?

    A) sys.dm_exec_query_stats
    B) sys.dm_os_waiting_tasks
    C) sys.dm_db_index_usage_stats
    D) sys.dm_exec_cached_plans

    Show Answer

    Answer: B

    sys.dm_os_waiting_tasks is the live, real-time view of exactly what each session is currently blocked on.

    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 4 Quiz: SQL Server Execution Plan Analysis (10 Questions)

    Module 4 Quiz: SQL Server Execution Plan Analysis

    10 questions on reading plans like a diagnostician, not just a spectator.

    1. Why can an operator’s “cost %” in a plan be misleading?

    A) It’s always 100% accurate
    B) It’s computed from row ESTIMATES, which can be wrong (parameter sniffing, stale stats)
    C) SQL Server doesn’t actually compute cost
    D) Cost % only applies to INSERT statements

    Show Answer

    Answer: B

    Cost % is pre-execution and estimate-based — exactly the same estimates that can be wrong per Module 3’s cardinality estimation lesson.

    2. What should you always capture in SSMS, and why, when diagnosing a real slow query?

    A) Estimated plan — it’s faster to view
    B) Actual plan (Ctrl+M) — estimated plans never show real row counts
    C) Neither matters
    D) Only the query text

    Show Answer

    Answer: B

    The actual plan includes real row counts per operator, which is what lets you compare against estimates and spot the gap.

    3. What does a large gap between Estimated and Actual rows on one operator most strongly suggest?

    A) Nothing meaningful
    B) Something upstream (stale stats, non-SARGable predicate, parameter sniffing) is misleading the optimizer
    C) A hardware failure
    D) The query is definitely correct

    Show Answer

    Answer: B

    This is often the single strongest diagnostic signal in a plan — more informative than cost % alone.

    4. What does a “thick arrow” between two operators in a graphical plan represent?

    A) A faster operation
    B) A large number of rows flowing between those operators
    C) An error
    D) A parallel operation always

    Show Answer

    Answer: B

    Arrow thickness is proportional to row count — trace thick arrows back to find where row counts unexpectedly balloon.

    5. What does the XML string “PlanAffectingConvert” indicate when found in a plan?

    A) A successful index seek
    B) An implicit data type conversion that affected the chosen plan
    C) A backup operation
    D) A parallelism warning only

    Show Answer

    Answer: B

    This directly ties back to the SARGability-killing implicit conversions covered in Module 3.

    6. What does “SpillToTempDb” in a plan’s XML indicate?

    A) A successful query
    B) A Hash Match or Sort ran out of memory and spilled to disk — a serious performance flag
    C) A backup is running
    D) tempdb is corrupted

    Show Answer

    Answer: B

    Spills mean the memory grant wasn’t enough for the operation, forcing much slower disk-based processing.

    7. What does “NoJoinPredicate” in plan XML often indicate?

    A) A well-optimized join
    B) An accidental CROSS JOIN, often from a missing join condition
    C) A missing index only
    D) A columnstore index

    Show Answer

    Answer: B

    This is a common accidental-bug signature — a forgotten or mistyped join condition producing a full Cartesian product.

    8. How can you search the plan cache for every currently-cached plan that spills to tempdb?

    A) It’s not possible
    B) CROSS APPLY sys.dm_exec_query_plan() and search the XML text for ‘SpillToTempDb’
    C) Only via SQL Server Profiler
    D) Only by restarting the server

    Show Answer

    Answer: B

    Casting the query_plan XML to text and searching it across sys.dm_exec_cached_plans lets you proactively hunt server-wide, not just query-by-query.

    9. Are yellow warning triangles in a graphical plan safe to ignore if the query “seems fine”?

    A) Yes, always
    B) No — they flag real issues like implicit conversions or missing statistics worth investigating
    C) They only appear on errors
    D) They mean the query failed

    Show Answer

    Answer: B

    Warning icons are the plan actively telling you something is off — don’t dismiss them just because the query technically returned results.

    10. Why is SET SHOWPLAN_XML useful beyond just viewing the graphical plan?

    A) It isn’t useful
    B) It exposes the raw plan structure so you can search/query it programmatically across many plans
    C) It only works on SELECT statements
    D) It replaces the need for indexes

    Show Answer

    Answer: B

    Wide plans are hard to scan visually — the underlying XML lets you search for specific warning strings directly, or automate the search across the whole plan cache.

    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.