https://sqlmaster.mssqlplanner.com

Tag: SQL Server

  • 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 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 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 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 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 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 2 Quiz: SQL Server Indexing Strategy (10 Questions)

    Module 2 Quiz: SQL Server Indexing Strategy

    10 questions covering index decision-making, fragmentation, statistics, and columnstore — including the classic “always rebuild” misconception.

    1. What does sys.dm_db_missing_index_details actually represent?

    A) Indexes that were deleted
    B) Indexes the optimizer logged wishing existed for past queries
    C) Corrupted indexes
    D) A list of all indexes on the server

    Show Answer

    Answer: B

    It’s a hypothesis generator based on what the optimizer wanted, not a guaranteed-correct instruction — always verify against real query patterns first.

    2. Why might an index with high user_seeks still be a bad index to keep?

    A) This is never the case
    B) If its write-maintenance cost on a heavily-written table outweighs the read benefit
    C) Seeks are always bad
    D) High seeks always mean the index is perfect

    Show Answer

    Answer: B

    Every index decision is a trade-off — reads must be weighed against write cost on the same table, not evaluated in isolation.

    3. At what fragmentation level does REORGANIZE typically become the recommended action?

    A) 0-5%
    B) 5-30%
    C) Always, regardless of level
    D) Only above 90%

    Show Answer

    Answer: B

    Below 5%, do nothing. 5-30%, REORGANIZE (lighter, always online). Above 30%, REBUILD is the standard threshold.

    4. “Gotcha”: Is “always rebuild indexes weekly regardless of fragmentation” a sound practice?

    A) Yes, it’s always correct
    B) No — it wastes CPU/IO on indexes that were never fragmented enough to matter
    C) Rebuilding has no cost
    D) Fragmentation is irrelevant to performance

    Show Answer

    Answer: B

    This is one of the most common DBA misconceptions — measure fragmentation first (sys.dm_db_index_physical_stats), don’t schedule blind maintenance.

    5. A small, rarely-scanned table shows 60% fragmentation. How much does this typically matter?

    A) Critical, fix immediately
    B) Usually very little — fragmentation mainly affects large tables read via range scans
    C) It always causes corruption
    D) It doubles storage cost

    Show Answer

    Answer: B

    A tiny table accessed via seeks is largely unaffected by fragmentation — context (table size, access pattern) always matters more than the raw percentage.

    6. What typically triggers SQL Server’s automatic statistics update, by default?

    A) Every single INSERT
    B) Roughly 20% of rows changing (with newer granular thresholds for large tables)
    C) Never automatically
    D) Only on server restart

    Show Answer

    Answer: B

    The classic threshold is ~20% row modification; recent SQL Server versions added more granular auto-update behavior for very large tables.

    7. A query suddenly gets a bad plan right after a huge bulk load. What’s the most likely first suspect?

    A) Stale statistics not yet reflecting the new data volume
    B) Corrupted transaction log
    C) A hardware failure
    D) Buffer pool being too large

    Show Answer

    Answer: A

    Bulk loads change row counts dramatically; if auto-update hasn’t caught up, the optimizer estimates against outdated statistics — check sys.dm_db_stats_properties.

    8. What’s the core structural difference between rowstore and columnstore indexes?

    A) No real difference
    B) Rowstore stores full rows together; columnstore stores each column together, compressed
    C) Columnstore is just a faster rowstore
    D) Rowstore is only for backups

    Show Answer

    Answer: B

    This structural difference is exactly why columnstore compresses better and scans faster for analytical aggregation, but is worse for single-row OLTP lookups.

    9. What execution mode do columnstore index queries typically use that boosts performance on large scans?

    A) Row mode
    B) Batch mode — processing ~900 rows per operator call
    C) Single-threaded mode only
    D) There’s no special execution mode

    Show Answer

    Answer: B

    Batch mode dramatically reduces per-row CPU overhead for large aggregations compared to traditional row-by-row execution.

    10. Why is a columnstore index usually a poor primary index choice for an OLTP order-lookup table?

    A) Columnstore is always slower than rowstore
    B) It’s optimized for bulk scan/aggregate patterns, not frequent single-row point lookups/updates
    C) Columnstore can’t store integers
    D) It’s deprecated

    Show Answer

    Answer: B

    “Fetch order #4471” is exactly the point-lookup pattern columnstore is not designed for — reserve it for fact tables and reporting/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.