SQL Server Performance Tuning - Complete Guide

SQL Server performance tuning is the practice of making queries faster through proper indexing, SARGable query patterns, execution-plan analysis, and schema design that matches your access patterns. The database is usually the first real bottleneck an application hits, and the fixes are often dramatic: a single well-designed non-clustered index can turn a full table scan over millions of rows into a seek that reads a handful of pages. The core techniques are knowing when to use clustered versus non-clustered indexes, putting the most selective column first in composite indexes, avoiding functions on indexed columns so predicates stay SARGable, reading execution plans for scans and key lookups, and batching large updates to avoid lock escalation. SQL Server even tells you what it is missing through the missing-index DMVs. This guide covers each technique with runnable T-SQL, from index design to deadlock prevention and monitoring.

How Do SQL Server Indexes Work?

Clustered vs Non-Clustered

A clustered index determines the physical order of data in a table. You can only have one per table. Non-clustered indexes are separate structures that point to the data.

sql

-- Clustered index (usually on primary key)
CREATE CLUSTERED INDEX IX_Orders_Id ON Orders(Id);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_UserId ON Orders(UserId);

-- Composite index (multiple columns)
CREATE NONCLUSTERED INDEX IX_Orders_UserId_Status
ON Orders(UserId, Status)
INCLUDE (TotalAmount, CreatedAt);

-- Unique index
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email ON Users(Email);

-- Filtered index (partial index)
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(UserId, CreatedAt)
WHERE Status = 'Active';

Index Column Order Matters

sql

-- Index on (UserId, Status)
CREATE INDEX IX_Orders_UserId_Status ON Orders(UserId, Status);

-- This query CAN use the index
SELECT * FROM Orders WHERE UserId = @UserId AND Status = 'Active';

-- This query CAN use the index (partial match on first column)
SELECT * FROM Orders WHERE UserId = @UserId;

-- This query CANNOT efficiently use the index (missing first column)
SELECT * FROM Orders WHERE Status = 'Active';

-- Rule: Always put the most selective column first
-- and columns used in equality (=) before range (<, >, BETWEEN)

How Do You Optimize Queries?

Avoid SELECT *

sql

-- BAD: Retrieves all columns, can't use covering index
SELECT * FROM Orders WHERE UserId = @UserId;

-- GOOD: Only retrieve needed columns
SELECT Id, Status, TotalAmount, CreatedAt
FROM Orders
WHERE UserId = @UserId;

-- With covering index, this avoids key lookup
CREATE INDEX IX_Orders_UserId_Covering
ON Orders(UserId)
INCLUDE (Status, TotalAmount, CreatedAt);

SARGable Queries

SARGable (Search ARGument able) queries can use indexes efficiently. Avoid functions on indexed columns.

sql

-- NON-SARGable: Function on indexed column, full table scan
SELECT * FROM Orders WHERE YEAR(CreatedAt) = 2024;

-- SARGable: Index can be used
SELECT * FROM Orders
WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01';

-- NON-SARGable: LIKE with leading wildcard
SELECT * FROM Users WHERE Email LIKE '%@gmail.com';

-- SARGable: LIKE without leading wildcard
SELECT * FROM Users WHERE Email LIKE 'john%';

-- NON-SARGable: Function on column
SELECT * FROM Users WHERE UPPER(LastName) = 'SMITH';

-- SARGable: Use computed column with index instead
ALTER TABLE Users ADD LastNameUpper AS UPPER(LastName);
CREATE INDEX IX_Users_LastNameUpper ON Users(LastNameUpper);
SELECT * FROM Users WHERE LastNameUpper = 'SMITH';

-- NON-SARGable: Implicit conversion
-- If UserId is VARCHAR but you pass INT
SELECT * FROM Orders WHERE UserId = 12345;

-- SARGable: Match data types
SELECT * FROM Orders WHERE UserId = '12345';

How Do You Read Execution Plans?

sql

-- View estimated execution plan
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE UserId = @UserId;
GO
SET SHOWPLAN_XML OFF;

-- View actual execution plan with statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM Orders WHERE UserId = @UserId;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Find missing indexes suggested by SQL Server
SELECT
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(REPLACE(
        ISNULL(mid.equality_columns, '') + ISNULL(mid.inequality_columns, ''),
        '[', ''), ']', ''), ', ', '_')
    + ' ON ' + mid.statement
    + ' (' + ISNULL(mid.equality_columns, '')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL(mid.inequality_columns, '') + ')'
    + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Key Operators to Watch

Warning signs in execution plans:

Table Scan / Clustered Index Scan - Reading entire table, add appropriate index
Key Lookup - Extra I/O, consider INCLUDE columns in index
Sort - Expensive operation, consider index with ORDER BY columns
Hash Match - Large memory grants, may indicate missing indexes
Parallelism - Can be good or bad, watch for CXPACKET waits

Common Performance Patterns

Pagination

sql

-- BAD: OFFSET/FETCH without proper index
SELECT *
FROM Orders
ORDER BY CreatedAt DESC
OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY;

-- GOOD: Keyset pagination (much faster for large offsets)
SELECT TOP 20 *
FROM Orders
WHERE CreatedAt < @LastSeenCreatedAt
    OR (CreatedAt = @LastSeenCreatedAt AND Id < @LastSeenId)
ORDER BY CreatedAt DESC, Id DESC;

-- Make sure you have supporting index
CREATE INDEX IX_Orders_CreatedAt_Id ON Orders(CreatedAt DESC, Id DESC);

EXISTS vs IN vs JOIN

sql

-- For checking existence, EXISTS is usually fastest
-- EXISTS stops at first match

-- GOOD for small result sets
SELECT *
FROM Orders o
WHERE EXISTS (
    SELECT 1 FROM Users u
    WHERE u.Id = o.UserId AND u.IsActive = 1
);

-- IN can be efficient for small lists
SELECT * FROM Orders WHERE Status IN ('Pending', 'Processing', 'Shipped');

-- For large lists, temp table may be better
CREATE TABLE #StatusList (Status VARCHAR(50) PRIMARY KEY);
INSERT INTO #StatusList VALUES ('Pending'), ('Processing'), ('Shipped');

SELECT o.*
FROM Orders o
INNER JOIN #StatusList s ON o.Status = s.Status;

Avoiding Parameter Sniffing Issues

sql

-- Parameter sniffing can cause poor plan reuse
-- SQL Server compiles plan based on first parameter value

-- Option 1: OPTIMIZE FOR hint
CREATE PROCEDURE GetOrdersByUser
    @UserId UNIQUEIDENTIFIER
AS
SELECT * FROM Orders
WHERE UserId = @UserId
OPTION (OPTIMIZE FOR (@UserId UNKNOWN));

-- Option 2: RECOMPILE (use sparingly, adds CPU overhead)
CREATE PROCEDURE GetOrdersByDateRange
    @StartDate DATE,
    @EndDate DATE
AS
SELECT * FROM Orders
WHERE CreatedAt BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

-- Option 3: Local variables (hides parameter from optimizer)
CREATE PROCEDURE GetOrdersByUser
    @UserId UNIQUEIDENTIFIER
AS
DECLARE @LocalUserId UNIQUEIDENTIFIER = @UserId;
SELECT * FROM Orders WHERE UserId = @LocalUserId;

Index Maintenance

sql

-- Check index fragmentation
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganize (online, for 10-30% fragmentation)
ALTER INDEX IX_Orders_UserId ON Orders REORGANIZE;

-- Rebuild (offline by default, for >30% fragmentation)
ALTER INDEX IX_Orders_UserId ON Orders REBUILD;

-- Online rebuild (Enterprise edition)
ALTER INDEX IX_Orders_UserId ON Orders REBUILD WITH (ONLINE = ON);

-- Update statistics
UPDATE STATISTICS Orders;

-- Rebuild all indexes on a table
ALTER INDEX ALL ON Orders REBUILD;

Query Patterns

Batch Updates

sql

-- BAD: Updating millions of rows in one transaction
UPDATE Orders SET Status = 'Archived' WHERE CreatedAt < '2020-01-01';

-- GOOD: Batch updates to avoid lock escalation and log growth
DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET Status = 'Archived'
    WHERE CreatedAt < '2020-01-01' AND Status != 'Archived';

    SET @RowsAffected = @@ROWCOUNT;

    -- Optional: Add delay to reduce blocking
    WAITFOR DELAY '00:00:01';
END

Efficient Aggregations

sql

-- For running totals, use window functions
SELECT
    Id,
    Amount,
    SUM(Amount) OVER (ORDER BY CreatedAt ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Transactions;

-- For counts by category, indexed view can help
CREATE VIEW vw_OrderCountByStatus
WITH SCHEMABINDING
AS
SELECT
    Status,
    COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY Status;
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_OrderCountByStatus
ON vw_OrderCountByStatus(Status);

-- Query the view instead of aggregating on the fly
SELECT * FROM vw_OrderCountByStatus;

How Do You Prevent Deadlocks?

sql

-- Access tables in consistent order across all procedures
-- If Proc A: Orders -> OrderItems
-- Then Proc B: Orders -> OrderItems (same order!)

-- Use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For read-heavy workloads, consider snapshot isolation
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Keep transactions short
BEGIN TRANSACTION;
    -- Do the minimum work necessary
    UPDATE Orders SET Status = 'Shipped' WHERE Id = @OrderId;
    INSERT INTO OrderHistory (OrderId, Status, ChangedAt) VALUES (@OrderId, 'Shipped', GETUTCDATE());
COMMIT;

-- Use NOLOCK hint sparingly (dirty reads) for reporting queries
SELECT COUNT(*) FROM Orders WITH (NOLOCK) WHERE Status = 'Active';

How Do You Find Expensive Queries?

sql

-- Find most expensive queries by CPU
SELECT TOP 20
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    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 avg_cpu_time DESC;

-- Find queries with most reads
SELECT TOP 20
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    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 avg_logical_reads DESC;

-- Currently running queries
SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    t.text AS query_text,
    r.cpu_time,
    r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';

-- Index usage statistics
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

Table Design Tips

Design for performance:

Use appropriate data types - INT vs BIGINT, VARCHAR(100) vs VARCHAR(MAX)
Avoid NULLable columns when possible - they add storage overhead
Consider column order - fixed-width columns first
Use computed columns for frequently calculated values
Partition large tables by date or category
Archive old data to separate tables

sql

-- Table partitioning example
CREATE PARTITION FUNCTION pf_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate ALL TO ([PRIMARY]);

CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER NOT NULL,
    UserId UNIQUEIDENTIFIER NOT NULL,
    CreatedAt DATE NOT NULL,
    -- other columns
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (Id, CreatedAt)
) ON ps_OrderDate(CreatedAt);

The fastest query is the one you don't have to run. Cache aggressively at the application level, design your schema for your access patterns, and only optimize queries that actually need it. Measure first, then optimize.

Database performance tuning is an ongoing process. Set up monitoring, establish baselines, and review execution plans regularly. The best-performing databases are those designed with performance in mind from the start.

SQL Server Performance: Indexing, Query Optimization & Best Practices