SQL Server Performance Tuning: A Production Workflow

Session overview

Performance incidents reward a calm, repeatable process and punish guesswork. This session walks through the investigation order from SQL Server Performance Tuning: A Practical Checklist, live, on a deliberately troubled demo database. It moves from scope to evidence, to the specific query, to one controlled change, and then measures again.

1. 1ScopeTime window, database, application
2. 2Active requests and waitsWhat is running, and what it waits on
3. 3BlockingHead blocker and chain
4. 4Expensive queriesBy total impact, not just the slowest
5. 5Plan, statistics, indexesWhy this query does this much work
6. 6Change and measureOne variable, with a baseline

Outline

1. Start with evidence: what to capture before touching anything.
2. DMVs for incidents: requests, sessions, waits and query statistics.
3. Blocking: reading the chain and capturing the head blocker.
4. Execution plans: estimated vs actual rows, warnings, and why cost percentages mislead.
5. Query Store: finding regressions and using plan forcing carefully.
6. Statistics and indexes: as a system, especially on very large tables.
7. Changing safely: one variable, a baseline, and a rollback path.

Adapting the session

• Workshop: attendees diagnose three prepared scenarios (blocking, a plan regression and a missing index) using only DMVs and Query Store.