SQL Server Performance Tuning: Indexing and Query Guide

Why SQL Server performance still matters

Despite the NoSQL wave and cloud-managed databases, SQL Server remains the backbone of enterprise applications. Most performance problems are not SQL Server’s fault — they are configuration, query design, or infrastructure problems that are entirely fixable.

This guide covers the techniques I use when diagnosing SQL Server performance issues in production environments.

1. Wait stats — start here

Wait stats tell you what SQL Server is waiting for. This is the single most valuable diagnostic:

-- Top 10 wait types by total wait time
SELECT TOP 10
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_sec,
    signal_wait_time_ms / 1000.0 AS signal_wait_sec,
    waiting_tasks_count,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() 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',
    'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
)
ORDER BY wait_time_ms DESC;

Common wait types and what they mean

2. Index optimization

Missing indexes are the #1 cause of slow queries. SQL Server tells you exactly which indexes it wants:

-- Missing indexes with impact score
SELECT TOP 20
    ROUND(s.avg_total_user_cost * s.avg_user_impact
        * (s.user_seeks + s.user_scans), 0) AS improvement_measure,
    d.statement AS table_name,
    'CREATE INDEX IX_' +
        REPLACE(REPLACE(d.statement, '[', ''), ']', '') +
        '_' + CAST(NEWID() AS VARCHAR(8))
        + ' ON ' + d.statement
        + ' (' + ISNULL(d.equality_columns, '')
        + CASE WHEN d.equality_columns IS NOT NULL
               AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL(d.inequality_columns, '') + ')'
        + ISNULL(' INCLUDE (' + d.included_columns + ')', '')
    AS create_index_statement
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 improvement_measure DESC;

Index maintenance

Fragmented indexes degrade performance over time:

-- Find fragmented indexes
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
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.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Rule of thumb:

• 10-30% fragmentation → ALTER INDEX REORGANIZE
• Over 30% → ALTER INDEX REBUILD
• Under 1000 pages → do not bother, the overhead is not worth it

3. Query Store

Query Store (available since SQL Server 2016) tracks query performance over time. Enable it:

ALTER DATABASE [YourDB] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 30,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO
);

Find regressed queries:

-- Queries that got worse recently
SELECT TOP 10
    q.query_id,
    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,
    p.plan_id
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.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
JOIN sys.query_store_runtime_stats_interval rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

4. Memory configuration

SQL Server will consume all available memory by default. Set max server memory to leave room for the OS:

-- Check current memory config
SELECT
    name,
    value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

Sizing guideline for dedicated SQL Server:

Leave 4 GB minimum for the OS on smaller servers, 8-10% on larger ones.

-- Set max memory (in MB)
EXEC sp_configure 'max server memory', 26624;
RECONFIGURE;

Buffer pool pressure

Check if SQL Server is under memory pressure:

-- Page Life Expectancy — how long pages stay in cache
SELECT
    object_name,
    counter_name,
    cntr_value AS page_life_expectancy_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
    AND object_name LIKE '%Buffer Manager%';

PLE under 300 seconds on a production server indicates memory pressure — SQL Server is evicting pages from cache faster than it should.

5. tempdb optimization

tempdb is a shared resource used by every connection. A slow tempdb bottlenecks everything:

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

tempdb best practices

Number of data files: Match the number of logical CPU cores, up to 8. Beyond 8, add in groups of 4 only if contention persists:

-- Add tempdb files (do this at a maintenance window)
ALTER DATABASE tempdb
ADD FILE (
    NAME = 'tempdb2',
    FILENAME = 'T:\tempdb\tempdb2.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB
);

Equal sizing: All tempdb files must be the same size. SQL Server uses a proportional fill algorithm — unequal files cause hotspots.

Trace Flag 1118: Eliminates mixed extent allocation contention. Enabled by default in SQL Server 2016+.

6. MAXDOP and Cost Threshold

Two settings that are almost always wrong on default installs:

-- Check current values
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (
    'max degree of parallelism',
    'cost threshold for parallelism'
);

MAXDOP guidelines

EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

Cost Threshold for Parallelism

The default value of 5 is far too low for modern hardware. Most production servers should use 25-50:

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

This prevents small queries from going parallel and wasting thread resources.

7. Monitoring queries

Currently running expensive queries

SELECT TOP 10
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.cpu_time,
    r.total_elapsed_time / 1000 AS elapsed_sec,
    r.reads,
    r.writes,
    SUBSTRING(t.text, r.statement_start_offset/2 + 1,
        (CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset) / 2 + 1) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Blocking chains

SELECT
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time / 1000 AS wait_sec,
    blocker.session_id AS blocker_session,
    blocker_text.text AS blocker_query
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;

I/O latency per database

SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_latency_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_latency_ms,
    vfs.num_of_reads,
    vfs.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;

Acceptable latency: Under 10ms for data files, under 5ms for transaction log files. Anything above 20ms indicates a storage bottleneck.

8. Automated maintenance

Set up a maintenance plan using Ola Hallengren’s maintenance scripts — the industry standard:

-- Index maintenance
EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 1000;

-- Statistics update
EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL';

Performance tuning checklist

Related

• Databases on Kubernetes and Memory Overcommit
• FinOps for AI GPU Workloads
• Enterprise Kubernetes Security Checklist