SQL Server Performance Tuning: 15 Proven Techniques (2026)

The Performance Tuning Framework

Before diving into individual techniques, understand the diagnostic hierarchy:

Wait Statistics → Identify bottleneck category
     ↓
Query Store → Find expensive queries
     ↓
Execution Plans → Understand query behavior
     ↓
Index Analysis → Optimize data access paths
     ↓
Configuration → Server-level tuning

1. Wait Statistics Analysis

Wait stats tell you why SQL Server is slow. Every thread that waits registers its wait type.

-- Top 10 wait types (excluding benign waits)
SELECT TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_time_sec,
    signal_wait_time_ms / 1000.0 AS signal_wait_sec,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP',
    'CHECKPOINT_QUEUE', 'WAITFOR', 'XE_TIMER_EVENT',
    'BROKER_EVENTHANDLER', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
)
ORDER BY wait_time_ms DESC;

Wait Type	Bottleneck	Action
`PAGEIOLATCH_*`	Disk I/O	Add memory, faster storage, optimize queries
`CXPACKET`	Parallelism	Check MAXDOP, cost threshold
`LCK_M_*`	Locking	Optimize transactions, add indexes
`ASYNC_NETWORK_IO`	Client/network	Client not consuming results fast enough
`SOS_SCHEDULER_YIELD`	CPU pressure	Optimize queries, add CPU

2. Index Optimization

Missing Index DMV

-- Top 20 missing indexes by impact
SELECT TOP 20
    ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS [Impact],
    d.statement AS [Table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.user_seeks,
    s.user_scans
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
ORDER BY [Impact] DESC;

Unused Index Identification

-- Indexes that cost writes but provide no reads
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_updates AS WriteOps,
    s.user_seeks + s.user_scans + s.user_lookups AS ReadOps,
    (SELECT SUM(ps.used_page_count) * 8 / 1024
     FROM sys.dm_db_partition_stats ps
     WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id) AS SizeMB
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND s.user_seeks + s.user_scans + s.user_lookups = 0
    AND s.user_updates > 0
ORDER BY s.user_updates DESC;

3. Query Store Analysis

-- Enable Query Store
ALTER DATABASE [YourDB] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO
);

-- Top resource-consuming queries
SELECT TOP 20
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    rs.avg_duration / 1000.0 * rs.count_executions AS total_duration_ms
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(DAY, -7, GETUTCDATE())
ORDER BY total_duration_ms DESC;

4. Memory Configuration

-- Check current memory usage
SELECT
    physical_memory_in_use_kb / 1024 AS PhysicalMemoryUsedMB,
    locked_page_allocations_kb / 1024 AS LockedPagesMB,
    total_virtual_address_space_kb / 1024 AS VirtualAddressSpaceMB,
    process_physical_memory_low AS MemoryPressure
FROM sys.dm_os_process_memory;

-- Buffer pool usage by database
SELECT
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) * 8 / 1024 AS BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY BufferPoolMB DESC;

Best practice: Set max server memory to total RAM minus 4GB (for OS) minus memory for other services.

EXEC sp_configure 'max server memory (MB)', 28672;  -- 28GB on a 32GB server
RECONFIGURE;

5. TempDB Optimization

-- Check tempdb contention
SELECT
    session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
    AND resource_description LIKE '2:%';  -- database_id 2 = tempdb

Configuration rules:

Files: Match number of CPU cores (up to 8), all equal size
Location: Fastest available storage (NVMe preferred)
Growth: Fixed size increments (512MB-1GB), not percentage

-- Add tempdb files (run once per file needed)
ALTER DATABASE tempdb ADD FILE (
    NAME = 'tempdev2',
    FILENAME = 'T:\tempdb\tempdev2.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);

6. MAXDOP and Cost Threshold

-- Current settings
EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

-- Recommended settings
-- MAXDOP: min(8, num_cores_per_NUMA_node)
-- Cost threshold: 50 (default 5 is too low)
EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

7. Statistics Maintenance

-- Find outdated statistics
SELECT
    OBJECT_NAME(sp.object_id) AS TableName,
    sp.stats_id,
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS PctModified
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

-- Update all statistics with full scan
EXEC sp_updatestats;

8. Index Fragmentation

-- Check fragmentation for large tables
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.page_count * 8 / 1024 AS SizeMB
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000
    AND ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

Action thresholds:

10-30% fragmentation → ALTER INDEX REORGANIZE
Over 30% → ALTER INDEX REBUILD

9. Blocking and Deadlock Detection

-- Current blocking chains
SELECT
    blocked.session_id AS BlockedSession,
    blocked.wait_type,
    blocked.wait_time / 1000 AS WaitSeconds,
    blocker.session_id AS BlockerSession,
    blocker_text.text AS BlockerQuery
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocker ON blocked.blocking_session_id = blocker.session_id
CROSS APPLY sys.dm_exec_sql_text(blocker.most_recent_sql_handle) blocker_text
WHERE blocked.blocking_session_id > 0;

10. Parameter Sniffing Solutions

-- Option 1: OPTIMIZE FOR UNKNOWN
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- Option 2: RECOMPILE for volatile queries
SELECT * FROM Orders
WHERE OrderDate BETWEEN @Start AND @End
OPTION (RECOMPILE);

-- Option 3: Query Store plan forcing
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

11. Columnstore Indexes for Analytics

-- Add columnstore for reporting queries on large tables
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Orders_CS
ON Orders (OrderDate, CustomerID, TotalAmount, Status)
WHERE OrderDate > '2024-01-01';  -- Filtered for recent data

12. Read Committed Snapshot Isolation

Eliminates reader-writer blocking without dirty reads:

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON;
-- Requires exclusive access; plan for brief outage

13. Connection Pooling

// .NET connection string best practices
"Server=sql01;Database=AppDB;
 Min Pool Size=10;Max Pool Size=200;
 Connection Timeout=15;
 Application Name=WebAPI;
 MultipleActiveResultSets=false;"

14. Intelligent Query Processing (IQP)

Enable with compatibility level 160 (SQL Server 2022):

ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160;

Features enabled automatically:

Adaptive joins
Memory grant feedback (percentile-based)
Batch mode on rowstore
Approximate query processing
DOP feedback

15. Monitoring Dashboard Query

-- One-shot health check
SELECT
    'CPU' AS Metric,
    CAST(
        (SELECT TOP 1 SQLProcessUtilization
         FROM (SELECT record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
               FROM (SELECT CAST(record AS XML) AS record FROM sys.dm_os_ring_buffers
                     WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') x) y
    ) AS VARCHAR(10)) + '%' AS Value
UNION ALL
SELECT 'Buffer Cache Hit Ratio',
    CAST(CAST(a.cntr_value * 100.0 / b.cntr_value AS DECIMAL(5,2)) AS VARCHAR(10)) + '%'
FROM sys.dm_os_performance_counters a
CROSS JOIN sys.dm_os_performance_counters b
WHERE a.counter_name = 'Buffer cache hit ratio'
    AND b.counter_name = 'Buffer cache hit ratio base'
UNION ALL
SELECT 'Page Life Expectancy',
    CAST(cntr_value AS VARCHAR(10)) + ' sec'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy' AND object_name LIKE '%Buffer Manager%';