Index optimisation

Index findings are part of the Table and index checks Advisor runs whenever a job includes the Tables subject (see Performing an Analysis). There's no separate "index-only" mode: turn Tables on, and optionally narrow it further by category or by individual check, to get this set.

What Advisor checks

  • Duplicate indexes: two indexes on the same table with the same definition.
  • Overlapping indexes: indexes that share the same leading key columns but differ in included columns, column order or settings.
  • Missing index: an index SQL Server's own missing-index suggestions recommend adding.
  • Unused indexes: indexes SQL Server records no reads against.
  • Rarely used indexes: indexes with very low read counts.
  • Fragmented indexes: indexes with high fragmentation.
  • Index fill factor: an index whose fill factor setting looks wrong for how it's used.
  • No clustered index: a table with no clustered index.
  • Disabled indexes: indexes that exist but have been disabled.
  • Hypothetical indexes: indexes with metadata but no actual data.
  • Incorrectly named indexes: indexes whose name doesn't match your naming policy, see Custom Index Naming Policies.
  • Potentially unique indexes: indexes whose data happens to be unique already, so could be declared unique.

Reviewing what Advisor suggests

Select a finding, then choose View Script from its actions (or right-click it and choose the same option) to open the SQL that would resolve it. From there:

  • Copy copies the whole script to the clipboard.
  • Send to Script sends it to OmniScript, where you review it before running it against the server yourself.

Advisor never runs a script for you and never changes anything on the server. View Script is the only action any finding offers; there is no Apply or Fix button anywhere in Advisor.

Comparing indexes

Duplicate and overlapping findings also offer Compare Indexes: a side-by-side view of the index Advisor suggests keeping against the one it suggests dropping, or of a proposed replacement index against the ones it would replace. The comparison merges the included columns of the indexes involved onto the widest surviving index, so you can see exactly what the new index would cover before you create it.