Performance tuning without measurement is guesswork. SQL Server exposes wait statistics, DMVs, Query Store, and Extended Events — use them to find bottlenecks, apply targeted fixes, and verify improvements under load.

Tuning Methodology

  1. Establish baseline — CPU, disk latency, batch requests/sec, P95 query duration
  2. Identify bottleneck — waits, CPU, I/O, blocking, or memory pressure
  3. Hypothesize — missing index, stale stats, parameter sniffing, tempdb, MAXDOP
  4. Change one variable — isolate impact
  5. Verify — compare plans, logical reads, and business metrics
  6. Document — config changes and rationale in runbook

Never change max server memory and add indexes simultaneously — you won’t know what helped.

Wait Statistics 

  SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_sec,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE N'%SLEEP%'
  AND wait_type NOT LIKE N'%IDLE%'
  AND wait_type NOT LIKE N'%QUEUE%'
ORDER BY wait_time_ms DESC;
Wait Type Often Indicates
PAGEIOLATCH_* Disk I/O — slow storage, memory pressure
LCK_M_* Blocking — long transactions, missing indexes
CXPACKET / CXCONSUMER Parallelism imbalance — MAXDOP, skew
PAGEIOLATCH_EX on tempdb tempdb config — file count, size
ASYNC_NETWORK_IO Client not consuming results fast enough
WRITELOG Log disk bottleneck — commit-heavy workload

Reset waits only for testing windows:

  DBCC SQLPERF(N'waitstats', 1);

Memory Configuration 

  EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 24576;  -- Leave 4-8 GB for OS on 32 GB box
RECONFIGURE;

SELECT
    (total_physical_memory_kb / 1024) AS physical_mb,
    (available_physical_memory_kb / 1024) AS available_mb
FROM sys.dm_os_sys_memory;

Page Life Expectancy (Buffer Manager counter) — below 300 seconds sustained suggests memory pressure.

  SELECT cntr_value AS page_life_expectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy'
  AND object_name LIKE N'%Buffer Manager%';

tempdb Optimization

tempdb serves sorts, hashes, row versions (RCSI), and user temp objects:

  -- Multiple equally sized files (1 per CPU up to 8)
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = N'E:\TempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
-- Repeat for tempdev3..8

SELECT name, size * 8 / 1024 AS size_mb, growth * 8 / 1024 AS growth_mb
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');

Monitor version store:

  SELECT
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;

Parallelism and MAXDOP 

  -- Instance level
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Database scoped (2016+)
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

-- Query level
SELECT * FROM dbo.LargeFactTable WHERE Year = 2024 OPTION (MAXDOP 2);

Cost Threshold for Parallelism default 5 — raise to 50–100 on OLTP to reduce excessive parallelism on small queries.

Resource Governor

Throttle reporting workloads:

  CREATE RESOURCE POOL ReportPool WITH (MAX_CPU_PERCENT = 30, MAX_MEMORY_PERCENT = 30);
CREATE WORKLOAD GROUP ReportGroup USING ReportPool;

CREATE FUNCTION dbo.ClassifierFunction()
RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
    RETURN CASE
        WHEN APP_NAME() LIKE N'%ReportServer%' THEN N'ReportGroup'
        ELSE N'default'
    END;
END;
GO

CREATE CLASSIFIER FUNCTION dbo.ClassifierFunction;
ALTER RESOURCE GOVERNOR RECONFIGURE;

Useful when ad-hoc reports starve OLTP — not a substitute for proper indexing.

Index and Statistics Maintenance 

  -- Fragmentation-driven maintenance
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON, MAXDOP = 4);

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

Automate with Ola Hallengren’s MaintenanceSolution — industry standard scripts.

Query Store Overview 

  ALTER DATABASE MyApp SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 15
);

SELECT TOP 10
    qsq.query_id,
    qsrs.avg_duration / 1000.0 AS avg_ms,
    qsrs.count_executions,
    LEFT(qt.query_sql_text, 200) AS query_text
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
JOIN sys.query_store_query_text qt ON qsq.query_text_id = qt.query_text_id
ORDER BY qsrs.avg_duration DESC;

See the dedicated Query Store deep dive page for plan regression and forced plans.

Expensive Query DMVs 

  SELECT TOP 20
    total_elapsed_time / execution_count / 1000 AS avg_ms,
    execution_count,
    total_logical_reads / execution_count AS avg_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Plan cache clears on restart — Query Store persists history.

Common Mistakes

Mistake Effect
Tuning without baseline Cannot prove improvement
Adding indexes blindly Write regression
MAXDOP = 0 on OLTP CPU saturation on small queries
Ignoring blocking Misdiagnosed as “slow disk”
One tempdb file PFS/GAM contention
Disabling auto stats Bad plans after data change

Production Scenario: Morning Slowness

Symptoms: CPU 90%, CXPACKET waits spike at 8 AM.

Investigation:

  1. Query Store — top new query by duration
  2. Plan shows parallel scan on 200M row table
  3. Missing index on WHERE CreatedAt >= @today

Fix:

  CREATE NONCLUSTERED INDEX IX_Orders_CreatedAt ON dbo.Orders (CreatedAt) INCLUDE (Status, Total);

CPU drops to 25% — verify for one week.

Monitoring Tools

Tool Purpose
SSMS Activity Monitor Quick live view
sp_WhoIsActive Session-level detail
Extended Events Lightweight tracing
Azure Monitor / SCOM Enterprise alerting
SQL Sentry, Redgate Commercial deep monitoring

Troubleshooting Quick Reference 

  -- Active requests
SELECT session_id, status, command, wait_type, blocking_session_id, cpu_time, logical_reads
FROM sys.dm_exec_requests WHERE session_id > 50;

-- I/O stalls by file
SELECT DB_NAME(database_id) AS db, file_id, io_stall_read_ms, num_of_reads, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms DESC;

Measure first with waits and Query Store, then apply targeted fixes — not blanket configuration changes across production.