https://sqlmaster.mssqlplanner.com

Category: SQL Server Performance Tuning

Expert-level SQL Server performance tuning: architecture, indexing strategy, query optimization, execution plans, locking/concurrency, monitoring, and advanced performance features.

  • Memory-Optimized TempDB Metadata and Azure SQL Performance Specifics

    Memory-Optimized TempDB Metadata and Azure SQL Performance Specifics

    Two focused topics to close out the course: a direct engine-level fix for Module 5’s tempdb contention, and what genuinely changes when your target isn’t on-prem SQL Server anymore.

    Two ways the ground shifts under you(an engine fix, and a platform)TEMPDB METADATAbefore: PAGELATCH_EX pileup evenon the SYSTEM CATALOG pagesMEMORY_OPTIMIZEDTEMPDB_METADATA = ONlock & latch-free — even the catalog ✓+ON AZURE SQL DATABASExProfiler — gone, use XEventsxResource Governor — not availablextempdb files — managed for youEvidence-First workflow — identicalNote: Azure SQL Managed Instance sits in between —more on-prem surface than Azure SQL Database, stillless than a true on-prem or IaaS VM install. 📌

    Memory-Optimized TempDB Metadata: The Modern Fix

    ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Requires a service restart to take effect

    Rather than just spreading GAM/SGAM/PFS contention across multiple files (Module 5’s fix), this moves tempdb’s system tables and metadata structures themselves into lock/latch-free memory-optimized structures — directly eliminating the contention at its source rather than diluting it. Available since SQL Server 2019; genuinely the better long-term answer where supported.

    What Actually Changes on Azure SQL Database

    Concept from this course Azure SQL Database difference
    Buffer pool / memory (Module 1) Sized by your chosen service tier/vCore, not physical server RAM you control directly
    SQL Server Profiler Not available — Extended Events (Module 6) is the only option, which is exactly why this course taught XEvents thoroughly
    Resource Governor Not available on single/elastic pool databases — workload isolation instead comes from service tier/DTU-vCore choice itself
    tempdb file configuration Managed automatically — you don’t configure files directly

    Azure SQL Managed Instance sits in between: it supports far more of the on-prem surface (including SQL Agent, cross-database queries, and more configuration control) than Azure SQL Database, but still abstracts some infrastructure-level tuning compared to a true on-prem or IaaS VM install.

    The Takeaway for This Whole Course

    Every diagnostic principle — Evidence-First, SARGability, execution plans, DMVs, Query Store — applies identically on Azure. What changes is which infrastructure-level levers (Resource Governor, tempdb files, Profiler) are available to you versus abstracted away by the platform.

    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 Incident Report Template: How to Document a Performance Post-Mortem

    SQL Server Incident Report Template: How to Document a Performance Post-Mortem

    A fix without a written record teaches nothing to the next person who hits a similar symptom. This template mirrors the Evidence-First workflow directly, so writing the report is almost free once you’ve actually done the diagnosis.

    A post-mortem is just the workflow, written down(the report is free once you’ve done the work)BASELINEstep 2EVIDENCEstep 4ROOT CAUSEstep 5FIXstep 6VALIDATEstep 71. Summary — plain language, written for someone who wasn’t there3. Timeline — timestamped, start to finish8. Prevention — would monitoring (Module 6) have caught this sooner?A fix with no written record teaches nothing to thenext person who hits a similar symptom. 📌

    1. Summary

    One paragraph: what broke, for how long, and who/what was affected. Written for someone who wasn’t in the room.

    2. Baseline

    What did normal look like, with numbers? (duration, CPU, I/O, or whatever metric defines “working” for this system)

    3. Timeline

    Timestamped sequence: when was it first noticed, when did diagnosis start, when was the fix applied, when was it confirmed resolved.

    4. Evidence Captured

    Exactly what was captured and how (Extended Events session definition, DMV queries run, execution plan attached). Paste the actual queries — future-you will thank present-you.

    5. Root Cause

    The specific, technical cause — not “the database was slow,” but “missing nonclustered index on OrderLog.customer_id causing a clustered index scan on a 400M-row table.”

    6. Fix Applied

    The exact change made, plus why this specific fix (not a bigger rewrite, not a hardware upgrade) was the right scope.

    7. Validation

    Post-fix measurement against the Step 2 baseline, with numbers.

    8. Prevention

    Would monitoring (Module 6) have caught this earlier? Is this a pattern worth a standing alert or a schema review checklist item?

    Why the Structure Matters

    Following this template forces you to separate what happened from what you did about it from whether it actually worked — exactly the discipline the Evidence-First workflow teaches, just written down for the next person instead of held in your head.

    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.

  • Safely Testing SQL Server Performance Fixes: Capturing and Replaying Production Workloads

    Safely Testing SQL Server Performance Fixes: Capturing and Replaying Production Workloads

    Every fix in this course has been validated by re-running “a” query. In real production incidents, you need to validate against the real, full mixed workload before deploying — a synthetic single-query test can miss regressions elsewhere.

    Capture there, replay here — never mix them(the whole point is safety)NEVERPRODUCTIONreal data, real trafficnever touch directlycapture via XEventsCAPTURED TRACE.xel workload filepeak-hour windowrestore + replayISOLATED TEST COPY+ candidate fix applied(new index, forced plan…)compare vs baseline replayCompare aggregate duration & CPU against the pre-fixbaseline replay — not just one query in isolation.Restore a recent backup to a genuinely isolated copy first.The whole safety guarantee depends on that boundary. 📌

    Capturing a Real Workload

    -- Capture via an Extended Events session (modern approach, replaces old SQL Trace .trc capture)
CREATE EVENT SESSION WorkloadCapture ON SERVER
ADD EVENT sqlserver.rpc_completed, ADD EVENT sqlserver.sql_batch_completed
ADD TARGET package0.event_file (SET filename = N'WorkloadCapture')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION WorkloadCapture ON SERVER STATE = START;
-- Let it run for a representative window (e.g. peak business hours), then STOP

    Replaying It Against an Isolated Copy

    Production Captured Trace Isolated Test Copy(WITH your candidate fix applied)

    Restore a recent production backup to a genuinely isolated environment — never replay a captured production workload against anything that could touch real production data (it contains real inserts/updates/deletes). Apply your candidate fix (new index, forced plan, Resource Governor config) there, then replay the captured trace and compare aggregate duration/CPU against the pre-fix baseline replay.

    Why This Beats Testing One Query in Isolation

    A new index that speeds up the one query you were chasing can slow down a dozen other write-heavy statements in the real mixed workload — something a single-query test will never reveal. Full workload replay is how you catch that before it reaches production, not after.

    The Safety Rule

    Never test against production. Never replay a real captured workload containing real data changes against anything other than a genuinely isolated copy — the whole point is safety, and that guarantee only holds if the target environment can’t affect anything real.

    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 Performance Myths, Debunked: What Actually Deserves a Second Look

    SQL Server Performance Myths, Debunked: What Actually Deserves a Second Look

    Every one of these has been mentioned somewhere in this course already — collected here in one place as a final reference.

    Folklore, stamped(seven myths, one root cause)Rebuild ALL indexesweekly, no matter whatBUSTEDMore RAM always fixesa slow queryBUSTEDHigh cost % is alwaysthe real bottleneckBUSTEDAdding an index canonly ever helpBUSTEDPAGELATCH = LCK,same fix appliesBUSTEDNOLOCK on everythingis a free performance winBUSTEDThe root cause, every single time:skipping baseline + evidence, reaching for a remembered rule of thumb instead

    Myth: “Always rebuild all indexes weekly, regardless of fragmentation.”

    Truth:

    Measure fragmentation first (sys.dm_db_index_physical_stats). Below 5%, do nothing. A tiny, rarely-scanned table at 60% fragmentation often doesn’t matter at all. Blind scheduled rebuilds waste CPU/IO on indexes that never needed it.

    Myth: “More RAM always fixes a slow query.”

    Truth:

    More RAM only helps if the bottleneck is actually buffer pool pressure (pages being evicted and re-read from disk). It does nothing for a CPU-bound query, a lock-bound query, or a query with a genuinely bad plan from a missing index.

    Myth: “A high cost % operator in the execution plan is always the real bottleneck.”

    Truth:

    Cost % is computed from row estimates, which can be badly wrong under parameter sniffing or stale statistics. Cross-check against actual row counts — the Estimated-vs-Actual gap is often more informative than cost % alone.

    Myth: “Adding an index can only help, never hurt.”

    Truth:

    Every index adds write-maintenance cost to every INSERT/UPDATE/DELETE that touches it. An index that helps a rarely-run report but slows down a high-throughput write path is a net loss — always weigh read benefit against write cost.

    Myth: “PAGELATCH waits are the same problem as lock (LCK) waits, and the same fix (shorten transactions) applies.”

    Truth:

    Latches protect physical memory structures; locks protect logical transaction consistency. Applying lock-blocking fixes to a latch contention problem (like tempdb allocation contention) solves nothing — the fix is structurally different (e.g. more tempdb files, not shorter transactions).

    Myth: “WITH (NOLOCK) on every query is a safe, free performance win.”

    Truth:

    NOLOCK (READ UNCOMMITTED) permits dirty reads — data that may later be rolled back, or in rare cases, skipped/duplicated rows during concurrent page splits. It’s a genuine tool for tolerant reporting scenarios, not a default habit to apply blindly everywhere.

    Myth: “If a query is slow, the database server needs more powerful hardware.”

    Truth:

    The 13-Hour Delete case study earlier in this course was fixed with one targeted index, not a hardware upgrade. Diagnose with evidence first — hardware is sometimes genuinely the answer, but it’s usually the most expensive way to mask an unindexed query.

    The One Rule Underlying All of These

    Every single myth above traces back to skipping the Evidence-First workflow’s first two steps — baseline and capture evidence — and jumping straight to a remembered rule of thumb instead. That’s not a coincidence; it’s the whole thesis of this course, stated one more time on the way out.

    Every myth on this page shares the same root cause: a plausible-sounding rule applied without measurement. The Evidence-First workflow from the start of this course exists specifically to replace folklore with verified cause and effect.

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

    Module 7 Exercises: SQL Server Advanced Performance Labs

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

    Guided Labs

    Guided1. Create a MEMORY_OPTIMIZED_DATA filegroup and a simple memory-optimized table in a test database.
    Guided2. Write and call a natively compiled stored procedure against that table.
    Guided3. Create a resource pool capping CPU at 20%, a workload group, and a classifier function routing a specific test login into it.
    Guided4. Check whether memory-optimized tempdb metadata is currently enabled on your instance.

    SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
    Guided5. List which features from this entire course (Profiler, Resource Governor, tempdb files) would NOT be available if this workload were moved to Azure SQL Database.

    Challenge Scenarios

    Challenge6. A session-state table is experiencing the exact ever-increasing-key latch contention pattern from Module 5. Propose whether In-Memory OLTP or simply better key design is the more appropriate fix, and justify your choice.
    Challenge7. A shared instance runs both a mission-critical OLTP app and an analyst’s ad-hoc reporting tool that occasionally runs 100% CPU for minutes. Design a Resource Governor configuration to protect the OLTP workload.
    Challenge8. A team is migrating an on-prem SQL Server workload to Azure SQL Managed Instance and worried about losing Profiler and Resource Governor. Write a short migration note explaining what changes and what stays the same.

    Break-It Labs

    Break-It9. Deliberately run an unrestricted heavy query and observe its CPU consumption, then place it under a 10%-CPU-capped Resource Governor workload group and re-measure the difference in wall-clock duration.
    Break-It10. In a disposable test instance, deliberately disable memory-optimized tempdb metadata (if enabled) or simulate its absence by inducing heavy tempdb system-table churn, observe PAGELATCH contention on system tables specifically, then enable 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 6 Exercises: SQL Server Monitoring & Tooling Labs (10 Hands-On Exercises)

    Module 6 Exercises: SQL Server Monitoring & Tooling Labs

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

    Guided Labs

    Guided1. Enable Query Store on a test database and confirm its operation mode.

    ALTER DATABASE YourTestDb SET QUERY_STORE = ON;
SELECT actual_state_desc FROM sys.database_query_store_options;
    Guided2. Run the same query twice with different parameter selectivity, find both plans in Query Store, and force the better one.
    Guided3. Build a production-style Extended Events session filtered to duration > 2 seconds with a bounded rollover file target.
    Guided4. Add Page Life Expectancy, Batch Requests/sec, and Full Scans/sec to a PerfMon data collector set.
    Guided5. Read events back from an XEvents file target using sys.fn_xe_file_target_read_file.

    Challenge Scenarios

    Challenge6. A query performed well for months, then regressed after a deployment. Using Query Store, design a plan to find and force the pre-deployment plan without a code rollback.
    Challenge7. Design an Extended Events session to specifically catch queries causing tempdb spills, tying back to Module 4’s SpillToTempDb signature.
    Challenge8. A PerfMon dashboard shows Full Scans/sec climbing steadily over three months with no application changes. Propose which specific DMVs from this course you’d check next, in order.

    Break-It Labs

    Break-It9. Deliberately cause a query regression: force a bad plan via Query Store on a test query, observe degraded PerfMon/DMV metrics, then unforce it and confirm recovery.
    Break-It10. Deliberately create Extended Events overhead: run a session with NO duration filter capturing every statement on a busy test workload, observe the file size/overhead, then fix it with an aggressive filter 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 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.