Indexing Deep Dive: Real Performance Impact in SQL Server

Introduction

Indexes are not free. They improve read performance while adding write overhead, storage cost, and maintenance complexity. This post shows how to evaluate index impact using real metrics in SQL Server and how to verify the improvements from C#.

Baseline a Real Query

Start by capturing the query and its execution plan.

SET STATISTICS IO, TIME ON;

SELECT o.Id, o.CustomerId, o.TotalAmount
FROM Orders o
WHERE o.CustomerId = @CustomerId
ORDER BY o.CreatedAt DESC;

Look for table scans and high logical reads.

Create a Covering Index

CREATE NONCLUSTERED INDEX IX_Orders_Customer_CreatedAt
ON Orders (CustomerId, CreatedAt DESC)
INCLUDE (TotalAmount);

This index supports the predicate and ordering, and it covers TotalAmount to avoid a key lookup.

Validate the Impact

Re-run SET STATISTICS IO, TIME ON and compare logical reads and CPU time. The goal is fewer reads and lower latency.

C# Verification with ADO.NET

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new SqlCommand(
    "SELECT Id, CustomerId, TotalAmount FROM Orders WHERE CustomerId = @CustomerId ORDER BY CreatedAt DESC",
    connection
);
command.Parameters.AddWithValue("@CustomerId", customerId);

var stopwatch = Stopwatch.StartNew();
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    _ = reader.GetInt64(0);
}
stopwatch.Stop();

Console.WriteLine($"Query duration: {stopwatch.ElapsedMilliseconds} ms");

Avoid Over-Indexing

Every index increases write cost. Use these guidelines:

• Remove unused indexes based on DMV usage stats.
• Prefer composite indexes that match real access patterns.
• Avoid redundant indexes with similar prefixes.

Advanced Indexing Options

Filtered Indexes

CREATE NONCLUSTERED INDEX IX_Orders_Open
ON Orders (CreatedAt)
WHERE Status = 'Open';

Filtered indexes reduce size and boost performance for targeted queries.

Columnstore for Analytics

Columnstore indexes accelerate large scans for analytical workloads, but they are not optimal for OLTP queries.

Conclusion

Indexing is a measurement-driven process. Start with workload data, create the smallest index that supports the query, and verify improvements with statistics and execution plans. The fastest index is often the one you avoid creating.