SQL Server Performance Tuning & Troubleshooting

Topics covered

• Class 1 (free trial class), part 1: Find and fix a slow server
• Class objective: See an evidence-first investigation from symptom to measured fix. Instructor-led content: Introductions. Symptom, scope, time window and impact. The method: baseline, evidence, hypothesis, change, measurement, rollback. Separating query, concurrency, storage and capacity problems. Hands-on work: Run the provided DMV and wait queries against a slow lab workload. Find the top query, apply one fix and measure before and after. Student deliverable: Before-and-after measurement of one fix. Between-class practice: Capture a baseline of your lab under a controlled workload.
• Class 1 (free trial class), part 2: DMVs, live workload visibility and community tools
• Class objective: Read the current workload state quickly and reliably. Instructor-led content: dm_exec_requests, dm_exec_sessions and query text. Active transactions and resource use. Sampling caveats. sp_WhoIsActive and the First Responder Kit, and what they read underneath. Hands-on work: Identify the top active requests. Capture query text, wait type and resource use with DMVs and with sp_WhoIsActive. Student deliverable: Live workload worksheet. Between-class practice: Build a reusable DMV script.
• Class 1 (free trial class), part 3: Wait statistics and bottleneck classification
• Class objective: Classify where SQL Server spends its time. Instructor-led content: Instance waits vs. query waits. CPU, I/O, lock, memory and parallelism categories. Why waits without context mislead. Hands-on work: Generate controlled waits. Classify the likely resource. Student deliverable: Wait analysis report. Between-class practice: Record waits before and after a change.
• Class 1 (free trial class), part 4: Blocking, deadlocks and isolation trade-offs
• Class objective: Diagnose concurrency incidents and choose the right isolation level. Instructor-led content: Head blockers and open transactions. Deadlock graphs and the categories of fixes. Read committed snapshot: the benefit and its tempdb cost. Lock escalation. Hands-on work: Create blocking and deadlock scenarios. Capture evidence with DMVs and Extended Events. Test the same workload with and without row versioning. Student deliverable: Concurrency incident report. Between-class practice: Recommend fixes that don't rely on killing sessions.

Topics covered

• Class 2, part 1: Query Store configuration and data model
• Class objective: Use Query Store as the historical record of performance. Instructor-led content: The query, plan, runtime and wait stores. Capture mode, retention and size. Keeping Query Store healthy. Hands-on work: Configure Query Store. Find the top resource consumers over time. Student deliverable: Query Store setup checklist. Between-class practice: Check Query Store's state and size on your lab.
• Class 2, part 2: How the optimizer builds execution plans
• Class objective: Understand the optimizer's inputs and how it picks a plan. Instructor-led content: The cost model and alternative plans. Statistics, indexes and parameters as inputs. Compilation and the plan cache. Hands-on work: Compare the plans of equivalent queries. Identify the main operators and estimates. Student deliverable: Plan-reading worksheet. Between-class practice: Explain why a chosen plan can still be imperfect.
• Class 2, part 3: Estimated vs. actual plans and operator analysis
• Class objective: Read plans for suspicious work. Instructor-led content: Scans and seeks, joins, sorts, spools and lookups. Estimated vs. actual rows. Memory grants and spills. Hands-on work: Analyze prepared plans. Match operators to runtime metrics. Student deliverable: Execution-plan findings. Between-class practice: Capture and annotate one actual plan.
• Class 2, part 4: Cardinality, statistics and parameter sensitivity
• Class objective: Diagnose bad estimates and parameter-sensitive plans. Instructor-led content: Histograms and density. Stale statistics and data skew. Parameter sniffing, and parameter-sensitive plan optimization in SQL Server 2022 and later. Hands-on work: Change the data distribution and observe the plans. Test mitigation strategies. Student deliverable: Parameter sensitivity report. Between-class practice: Document the trade-offs of each mitigation.

Topics covered

• Class 3, part 1: Plan cache, recompilation and hints
• Class objective: Use cache controls and hints carefully. Instructor-led content: Plan reuse and cache pollution. OPTION (RECOMPILE), OPTIMIZE FOR and other hints. Hints as targeted, temporary interventions. Hands-on work: Inspect cache and compilation behavior. Compare a hinted and an unhinted workload. Student deliverable: Plan-cache worksheet. Between-class practice: Write the criteria for removing a hint.
• Class 3, part 2: Regressions, plan forcing and Query Store hints
• Class objective: Correct plan regressions safely. Instructor-led content: Finding regressed queries. The plan-forcing lifecycle. Query Store hints. Automatic plan correction. Hands-on work: Create and observe a regression. Force the previous plan or apply a hint, and verify. Student deliverable: Regression remediation report. Between-class practice: Define rollback criteria.
• Class 3, part 3: SARGability and query rewrite patterns
• Class objective: Rewrite queries so indexes can work. Instructor-led content: Functions on predicates. Implicit conversions. SELECT * and other unnecessary work. Hands-on work: Rewrite slow query patterns. Compare logical reads and plans. Student deliverable: Before-and-after query pack. Between-class practice: Explain why each rewrite helps.
• Class 3, part 4: Temp tables, table variables and intermediate results
• Class objective: Choose the right way to store intermediate results. Instructor-led content: Temp tables vs. table variables vs. CTEs. Their statistics and recompilation behavior. tempdb impact. Hands-on work: Compare three implementations of the same logic. Observe the plans and tempdb use. Student deliverable: Intermediate-results comparison. Between-class practice: Choose the best pattern for a new scenario.

Topics covered

• Class 4, part 1: Index design for performance
• Class objective: Choose indexes from real access patterns without over-indexing. Instructor-led content: Key column order and included columns. Seek vs. residual predicates. Key lookups and covering. Filtered indexes. Covering vs. overly wide indexes and their write cost. Hands-on work: Design alternative indexes for a slow workload. Measure reads, time and write overhead before and after. Student deliverable: Index design comparison. Between-class practice: Remove one redundant design from your solution.
• Class 4, part 2: Columnstore indexes and batch mode
• Class objective: Speed up reporting and analytical queries. Instructor-led content: When columnstore helps and when it hurts. Clustered vs. nonclustered columnstore. Batch mode, including batch mode on rowstore. Rowgroups, the deltastore and maintenance. Hands-on work: Add a columnstore index to a reporting workload. Compare plans, reads and time with the rowstore version. Student deliverable: Columnstore evaluation report. Between-class practice: Decide whether a given workload should use columnstore.
• Class 4, part 3: Statistics and cardinality maintenance
• Class objective: Decide when statistics maintenance is justified. Instructor-led content: Auto-update behavior. FULLSCAN vs. sampled updates. Incremental statistics. Hands-on work: Inspect statistics age and modification counters. Update selected statistics and compare the estimates. Student deliverable: Statistics maintenance decision. Between-class practice: Write a targeted statistics job.
• Class 4, part 4: Missing, duplicate and unused indexes
• Class objective: Consolidate index recommendations safely. Instructor-led content: Limits of the missing-index DMVs. Finding overlapping and duplicate indexes. Why usage statistics reset. Hands-on work: Analyze an over-indexed database. Propose a consolidated set of indexes. Student deliverable: Index consolidation plan. Between-class practice: Estimate the risk before dropping an index.

Topics covered

• Class 5, part 1: CPU pressure, parallelism and scheduler signals
• Class objective: Diagnose CPU-bound workloads. Instructor-led content: CPU saturation vs. healthy high CPU. SOS_SCHEDULER_YIELD. MAXDOP and cost threshold for parallelism. Hands-on work: Generate a CPU-heavy workload. Test a controlled parallelism change. Student deliverable: CPU diagnosis report. Between-class practice: Explain why server-wide changes need caution.
• Class 5, part 2: Memory grants, buffer pool and spills
• Class objective: Identify memory pressure. Instructor-led content: Buffer pool vs. workspace memory. RESOURCE_SEMAPHORE waits. Sort and hash spills, and memory grant feedback. Hands-on work: Find a memory grant problem. Observe a spill and tune the query. Student deliverable: Memory investigation. Between-class practice: Capture grants before and after a change.
• Class 5, part 3: I/O, storage latency and log throughput
• Class objective: Separate inefficient queries from storage bottlenecks. Instructor-led content: Read and write latency. PAGEIOLATCH vs. WRITELOG. Checkpoints and write behavior. Hands-on work: Measure file-level I/O. Tune one I/O problem. Student deliverable: I/O diagnosis. Between-class practice: List the evidence needed before escalating to the storage team.
• Class 5, part 4: tempdb, database settings and intelligent query processing
• Class objective: Use modern engine features appropriately. Instructor-led content: tempdb contention symptoms. Database-scoped configurations. Intelligent query processing features and which versions have them. Hands-on work: Inspect tempdb. Enable and test one appropriate feature. Student deliverable: Configuration change record. Between-class practice: Write the validation and rollback steps.

Topics covered

• Class 6, part 1: Incident lab: high CPU
• Class objective: Run an end-to-end CPU investigation. Instructor-led content: The triage timeline. Top queries, waits and plans. Immediate fix vs. lasting fix. Hands-on work: Investigate a generated incident. Present the evidence before fixing. Student deliverable: High-CPU incident report. Between-class practice: Prepare a summary for non-technical stakeholders.
• Class 6, part 2: Incident lab: blocking, deadlocks and I/O
• Class objective: Tell mixed symptoms apart. Instructor-led content: The blocking tree. Deadlock capture. Storage waits and escalation. Hands-on work: Resolve a mixed scenario. Document the root cause. Student deliverable: Mixed incident report. Between-class practice: Peer-review another student's diagnosis.
• Class 6, part 3: Incident lab: deployment regression
• Class objective: Recover from a release regression. Instructor-led content: Matching performance changes to deployments. Plan comparison in Query Store. Monitoring after the fix. Hands-on work: Identify the regressed query. Stabilize and validate it. Student deliverable: Regression recovery report. Between-class practice: Write a post-deployment monitoring checklist.
• Class 6, part 4: Final tuning capstone and performance report
• Class objective: Produce a tuning recommendation you can defend. Instructor-led content: The business objective. Measurement, intervention and validation. Remaining risk. Next steps, including the Azure SQL course. Hands-on work: Tune an assigned workload with evidence-backed changes. Present the baseline, gains, trade-offs and rollback. Student deliverable: Final performance report. Between-class practice: Final review.