Originally published at SQL & Cloud Lab.

This post was drafted with AI assistance.

Indexes are the single most effective way to speed up a slow SQL Server query, and also one of the easiest ways to make a database slower if you add them carelessly. This guide covers the core ideas a developer needs to design useful indexes, with scripts you can run today.

SQL Server index tuning checklist for beginners

Clustered vs nonclustered indexes

A clustered index defines the order in which the table's rows are stored. A table can have only one, and in most designs it is the primary key. A good clustering key is narrow, unique, static and ever-increasing, such as an INT IDENTITY or BIGINT.

A nonclustered index is a separate structure that stores the indexed columns in sorted order plus a pointer back to the row. You can have many of them, and each one speeds up certain reads while adding work to every insert, update and delete.

Index the columns you filter and join on

Start with the queries that matter most. Look at their WHERE, JOIN and ORDER BY clauses: those columns are your index candidates.

-- Query we want to speed up SELECT EmployeeId, PayCode, Amount FROM dbo.PayrollLine WHERE PayrollRunId = @RunId AND PayCode = 'BASIC'; -- Supporting index CREATE NONCLUSTERED INDEX IX_PayrollLine_Run_PayCode ON dbo.PayrollLine (PayrollRunId, PayCode);

Get the key column order right

SQL Server can seek on an index efficiently only from its leftmost column. Some practical rules:

  • Put columns used in equality filters (=) first.
  • Put columns used in range filters (>, BETWEEN, LIKE 'abc%') after them.
  • An index on (A, B) helps queries that filter on A, or on A and B, but usually not queries that filter only on B.

Cover queries with INCLUDE to avoid key lookups

If the index has the filter columns but not the selected columns, SQL Server must go back to the clustered index for each row. This shows up as a Key Lookup in the execution plan and gets expensive when many rows match.

Adding the selected columns with INCLUDE makes the index covering: the query is answered from the index alone. Included columns are stored only at the leaf level, so they do not affect the sort order.

CREATE NONCLUSTERED INDEX IX_PayrollLine_Run_PayCode ON dbo.PayrollLine (PayrollRunId, PayCode) INCLUDE (EmployeeId, Amount) WITH (DROP_EXISTING = ON);

Do not include every column "just in case". Wide indexes cost storage, memory and write time.

Find and remove unused indexes

Every index is maintained on every write, whether or not anything reads it. This query lists nonclustered indexes with many writes and few reads since the last restart:

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, ISNULL(s.user_seeks + s.user_scans + s.user_lookups, 0) AS reads, ISNULL(s.user_updates, 0) AS writes FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID() WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ORDER BY reads ASC, writes DESC;

Usage statistics reset when SQL Server restarts, so make sure the server has been running through a full business cycle, including month-end jobs, before dropping anything.

Treat missing-index suggestions with care

SQL Server records indexes it wishes it had in sys.dm_db_missing_index_details, and execution plans show them as green hints. They are useful clues, but the optimizer suggests them one query at a time. It does not consider existing indexes, write cost or overlap, and it often lists columns in a poor order. Combine similar suggestions into one well-designed index instead of creating each one.

Keep statistics up to date

The optimizer chooses plans using statistics about how values are distributed. Stale statistics on large tables can make a perfect index look unattractive. Keep AUTO_UPDATE_STATISTICS on, and for large tables that change heavily, schedule regular statistics updates as part of maintenance.

What about fragmentation?

On modern SSD storage, fragmentation matters much less than it used to. Rebuilding every index every night mostly burns CPU and log space. Focus on statistics and on indexes that genuinely support your queries; rebuild or reorganise only where you can measure a benefit.

Measure before and after

Always compare before and after. Run the query with SET STATISTICS IO, TIME ON and compare logical reads and CPU time, or compare the query in Query Store. Fewer logical reads is the clearest sign an index is helping.

SET STATISTICS IO, TIME ON; -- run the query here SET STATISTICS IO, TIME OFF;

Quick rules to remember

  • Index for your most important queries, not for every column.
  • Equality columns first, range columns next, selected columns in INCLUDE.
  • Review unused and duplicate indexes regularly.
  • Missing-index hints are suggestions, not instructions.
  • Prove the benefit with logical reads, not guesswork.

Related reading:

More SQL Server guides on my blog, SQL & Cloud Lab.