Index Analysis

Missing Indexes and Possible bad Indexes

The Index Analysis dashboard helps you prioritize index work by showing high-value missing index suggestions and identifying existing indexes that may be hurting performance because they attract writes but provide little read benefit.

The values in the dashboard are based on a snapshot of the last 24 hours.

Missing Indexes (top)

  • This table lists optimizer-suggested indexes and helps estimate potential query benefit:
    • Object Name: the table or view that would benefit from the index.
    • Advantage: a short description of the expected improvement (e.g., reduced logical reads).
    • Impact: an estimate of the relative benefit across the workload.
    • Equality columns: columns recommended for equality predicates (key columns).
    • Inequality columns: columns recommended for range/inequality predicates.
    • Included columns: non-key columns suggested for covering the queries.
    • User Seeks: number of seeks that could use the suggested index.
    • Last Seek: timestamp of the last observed seek opportunity.
    • Unique compiles: compilation counts that referenced the missing index.
    • User cost: estimated cumulative cost reduction if the index were present.
  • Use this table to find high-impact index candidates. Review the SQL that benefits, confirm selectivity and cardinality, and weigh expected read savings against write and storage costs before creating an index.

Possible Bad Indexes (below)

  • This table surfaces existing indexes that may be candidates for removal, consolidation, or rebuild because they incur write overhead without enough read benefit:
    • Database: database name containing the index.
    • Schema: schema name.
    • Table: table name.
    • Index: index name.
    • Total Writes: cumulative writes (inserts/updates/deletes) affecting the index.
    • Total Reads: cumulative reads (scans/seeks) that used the index.
    • Difference: Total Writes minus Total Reads (high positive values suggest write-heavy, low-used indexes).
    • Fill factor: configured fill factor for the index (indicates fragmentation risk).
    • Disabled: whether the index is disabled.
    • Hypothetical: whether the index is hypothetical (not actually created).
    • Filtered: whether the index uses a filter predicate.
  • Sort and filter the table to find indexes with large write cost and minimal read benefit. Investigate usage patterns and test removal or consolidation in a non-production environment before dropping indexes.

Controls and investigation tips

  • Filter by database, schema, or table to narrow scope.
  • Click a row to open index details (definition, sample queries, historical usage) and to download index DDL for review.
  • Consider the write cost, storage, and maintenance impact when acting on missing index suggestions. Missing-index estimates are heuristic — validate with query tests, execution plans, and Explain/actual runs.
  • For possible bad indexes, evaluate whether a filtered index, different key/included columns, or index consolidation would preserve read benefits while reducing write overhead.