Best Practices for SQL Server Performance Tuning

Best Practices for SQL Server Performance Tuning

In this lesson, you will learn the professional habits and standards that separate a “random fixer”
from a real SQL Server performance tuner. Most performance disasters do not come from one slow query.
They come from no baselines, no monitoring, guessing changes,
and no validation. This class gives you a repeatable best-practice framework you can apply on any system—
from a small website database to a large enterprise environment.

Class Overview (What students will learn today)

• How to build and use baselines (so you stop guessing).
• The “evidence-first” tuning workflow used in real production incidents.
• How to prioritize issues (top waits, top queries, top pain).
• How to tune without breaking correctness or stability.
• How to standardize troubleshooting (checklists, scripts, documentation).
• What to monitor continuously to prevent regressions.
• Common performance tuning traps and how to avoid them.

Learning Objectives (By the end of this class, students can…)

• Build a baseline that captures “normal performance.”
• Use a standard tuning workflow to diagnose bottlenecks.
• Prioritize issues using evidence (not opinions).
• Apply changes safely (one change at a time + validation).
• Document fixes in a way that prevents repeat incidents.
• Explain why many “internet best settings” are dangerous.

Part 1 — The #1 Best Practice: Don’t Guess (15–25 min)

Guessing causes unstable systems. It creates index bloat, random hints, unsafe configuration changes, and “fixes” that break later.
A professional tuner always uses proof:
measurements, before/after comparisons, and an understanding of the workload.

Part 2 — Baselines: The Foundation of Tuning (20–35 min)

A baseline answers: “What does normal look like?” Without it, you cannot prove improvement or detect regressions.
Baselines should be taken during normal business conditions and stored for comparison.

/* Baseline Snapshot (safe, read-only) */
SELECT GETDATE() AS captured_at;

SELECT
  cpu_count,
  scheduler_count,
  physical_memory_kb/1024 AS physical_memory_mb,
  sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT TOP (20)
  wait_type,
  wait_time_ms,
  waiting_tasks_count,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

Part 3 — The Professional Tuning Workflow (25–45 min)

Part 4 — Prioritization: Fix the Biggest Pain First (20–35 min)

In production, there are always multiple “issues.” Best practice is not to fix everything; it is to fix the issues that deliver
the greatest business impact with the least risk.

Part 5 — Safe Change Practices (Avoid Breaking Production) (25–45 min)

Many performance incidents become worse because someone applied a risky change with no rollback plan.
A DBA must tune with safety.

Part 6 — “Best Settings” Myth: Why Copy/Paste Tuning Fails (15–25 min)

Students often ask: “What are the best SQL Server settings?” The best practice answer:
there is no universal best setting. SQL Server configuration depends on workload, hardware, and business SLAs.

Part 7 — Preventing Regressions (Monitoring + Documentation) (20–40 min)

The job is not finished when performance improves today. The system must remain stable next week and next month.
Best practice is to prevent regressions using monitoring and documentation.

Student Lab (30–45 min) — “Build Your DBA Tuning Checklist”

Lab Outcome

Students will create a reusable checklist for diagnosing performance incidents and validating fixes.

Lab Tasks

1. Create a “Define the problem” template (SLA, time window, what changed).
2. Create a “Capture evidence” list (DMVs, Query Store, waits, top queries).
3. Create a “Bottleneck identification” section (CPU/I/O/memory/blocking/plan).
4. Create a “Fix + validation” section (before/after metrics + side effects).
5. Create a “Regression prevention” section (alerts, baselines, documentation).

Common Performance Tuning Mistakes (Teach These Early)

• Adding indexes blindly and creating write slowdown + storage bloat.
• Chasing one metric (CPU) without checking blocking, I/O, or memory.
• Changing multiple things at once (no learning, no control, high risk).
• Copy/pasting “best settings” without measuring the workload impact.
• Ignoring Query Store and missing plan regressions.
• Not validating fixes under concurrency and real workload.
• No documentation, causing the same incident to repeat.

Class Discussion Prompt

Homework Assignment

1. Create a one-page “Performance Tuning SOP” (standard operating procedure) for your DBA team.
2. Include: workflow, evidence requirements, safe change rules, and validation standards.
3. Add a “Top 10 scripts” section (DMVs, waits, blocking, Query Store).

Quick Knowledge Check (12 questions)

1. Why is “don’t guess” the #1 best practice?
2. What should a baseline include?
3. What are the main bottleneck categories you must identify?
4. Why is “one change at a time” important?
5. What is a regression and how can you detect it?
6. Why are universal “best settings” dangerous?
7. Give one example of a high-impact issue that should be prioritized first.
8. What is the role of Query Store in best practices?
9. What is the difference between total duration and average duration?
10. What is one safe rollback strategy for an index change?
11. Why must you validate under concurrency?
12. What documentation should be captured after a tuning change?

Closing (What’s next)

In the next lesson, we will cover Troubleshooting Common Performance Problems.
This will be a hands-on incident-style lesson where we walk through real symptoms (high CPU, high I/O, blocking,
memory pressure, TempDB issues, slow queries, and plan regressions) and apply a structured diagnostic process.