Indexes are the single highest-leverage tool in SQL Server performance work. A well-chosen index can turn a 40-second query into a 40-millisecond one; a poorly chosen one quietly taxes every write on the table. The discipline is not in adding indexes — it is in adding the right ones, in the right order, and knowing when to stop.

Clustered first: it defines the table

A clustered index is not an index on top of the table — it is the table, physically ordered by the key. Pick the column that is most often used to range-scan and that is monotonically increasing (an identity or a sequence). Avoid wide keys and highly volatile columns: every row insert then has to be physically relocated, and the cost compounds.

Nonclustered indexes: narrow and purposeful

Each nonclustered index is a separate B-tree that stores the key plus a pointer back to the row. The rule of thumb: the key should be selective, and the leading column should match the most common filter. If the query only needs a few more columns, add them as INCLUDE columns to make the index covering — the engine can then satisfy the query without ever touching the base table.

  • Lead with the most selective, most-filtered column.
  • Use INCLUDE for columns you read but do not filter on.
  • Keep keys short — every key column is stored in every index row.
  • One index per distinct access pattern, not one per column.
An index is a read optimization that you pay for on every write. Budget for both sides of the trade.

Read the plan, not the guess

Enable the actual execution plan and look for the tell-tale signs: a key lookup means the index is not covering; a table scan on a large table means no usable index exists; a sort or hash join on a column that should be indexed means the optimizer is doing work you could have removed. Then check sys.dm_db_index_usage_stats to see which indexes are actually being read versus merely written to.

If you are untangling a slow SQL Server estate — legacy T-SQL, missing indexes, or a warehouse that has outgrown its schema — our team in Bucharest does exactly this kind of production tuning for banks and enterprises across Europe.