Missing Indexes

Overview

SQL Server records every index it wishes it had. Left raw, that list is close to unusable

• it returns eight to thirty near-identical suggestions per table, it takes no account of the indexes the table already has, and it says nothing about what an index would cost to maintain.

This report fixes all three:

• Benefit is plotted against write cost. An index that saves a great deal of reading on a table nobody writes to is a different proposition from the same index on a table written a hundred thousand times, and the chart puts them in different places.
• Near-identical suggestions are consolidated into the one index that covers them.
• Key columns are put into the order an index actually has to be built in, rather than the order the DMV happened to return them.
• The caveats are printed under the headline rather than left implied.

Every database is covered except tempdb, whose object ids do not outlive the objects they name. Unlike the report this replaces, master, model and msdb are included – a missing index on msdb.dbo.sysjobhistory is a real and common finding.

What this fixes

• The old report gathered rows with sp_MSforeachdb, which is undocumented and known to skip databases, doing a USE and a full DMV read per database to collect something the DMVs already hold instance-wide. sys.dm_db_missing_index_details carries a database_id; no loop was ever needed.
• Its Impact score multiplied avg_user_impact without dividing by a hundred and then cast the result to BIGINT, so the number in the grid was a hundred times too large with its fractional part thrown away.

---

Where to find it

Route	How
Server tree	Right-click the server → Instance Level Reports → Missing Indexes
Server Overview page	Click the report link
Database level	The same report scoped to one database is on the Database Overview

The page title reads Missing Indexes for <server name>.

---

Requirements

• VIEW SERVER STATE on the instance.
• The read is awaited rather than run on the message loop, so a slow instance costs a loading panel over a live window rather than an application that has stopped responding. Past four hundred milliseconds the panel appears and counts the suggestions off as they arrive.
• Per-table context – row count, existing index count, whether an index already leads with the same column – needs a visit to each database. That visit is made only to the databases that actually appear in the fetched rows, so it costs the same on an instance with four hundred databases as on one with four.

---

The verdicts

Every suggestion lands in one of four quadrants, from its share of the instance’s total benefit and how heavily the table is written.

Verdict	When	What it means
Build first	5% or more of the benefit, table not heavily written	A real share of the reading, on a table that is barely written.
Weigh it	5% or more of the benefit, table heavily written	Worth having, but the table is written hard – measure the write cost first.
Skip	Under 5% of the benefit, table heavily written	Little benefit on a heavily written table – the upkeep likely costs more.
Marginal	Under 5% of the benefit, table not heavily written	Small share of the benefit – only worth it if it is cheap.

“Heavily written” is 100,000 write operations on the table. “A real share” is 5% of the instance’s total benefit.

The verdict is advice, not an instruction. It is a starting sort order for a list that otherwise arrives in no useful order at all.

---

Reading the chart

Each suggestion is a point: benefit on one axis, write cost on the other. The four quadrants correspond to the four verdicts, so Build first is visually a corner of the chart rather than a column you have to sort on.

Point size carries the magnitude of the benefit, so a large point in the Build first corner is the first thing to do.

The header lines

The summary line:

41 consolidated suggestions across 18 tables · top 3 = 62% of the benefit · 6 to build first · 4 on write heavy tables · 9 overlap an existing index

That last clause is the one the raw DMV cannot give you. Nine suggestions that overlap an index the table already has are usually nine suggestions you can dismiss.

The subtitle line carries the caveats:

top 100 of 412 suggestions · counted since the last service restart 21d 14h ago · suggestions ignore the indexes a table already has · every database except tempdb

It adds a warning when the instance holds a very large number of suggestion groups, which is itself a finding – it usually means a workload running un-parameterised ad hoc SQL.

---

Reading the grid

Column	What it is
Verdict	Build first, Weigh it, Skip or Marginal.
Benefit	The estimated benefit. Drawn with an in-cell bar so the grid scans like the chart.
% of Instance	Share of the instance’s total benefit.
Seeks	Seeks the optimizer would have used this index for.
Scans	Scans it would have used it for.
Impact	Average user impact, correctly scaled – the number the old report printed a hundred times too large.
Database	Which database the table is in.
Table	The table.
Table Rows	Row count, for judging whether the benefit is plausible.
Write Ops	Write operations against the table – the cost side of the decision.
Key Columns	In the order the index has to be built in.
Included Columns	The include list.
Notes	Context, such as an existing index already leading with the same column.
Last Wanted	When the optimizer last wanted it.

Key Columns are ordered correctly. The DMV returns equality and inequality columns separately, and building an index in the order it returns them produces an index that does not do what was asked for.

Last Wanted matters more than it looks. A suggestion the optimizer has not wanted for three weeks is describing a query that no longer runs.

---

The toolbar

Group	Buttons
Top N	Top 25 · Top 100 · Top 500
Consolidation	Consolidated · Every suggestion
	Refresh

Consolidated (the default) merges the near-identical suggestions the DMVs return per table into the single index that covers them. Every suggestion shows the raw list, which is worth seeing once to understand why consolidation exists.

The consolidator keys on database as well as table, so two databases with the same schema are never merged into one index belonging to neither.

---

Right-click actions

Item	What it does
Show Missing Index Advisor	The advisor dialog for that suggestion, including the CREATE INDEX statement.
Show Cardinality Report	Column cardinality for the table, which is what decides key column order.
Jump to Database Level	Opens the same report scoped to that database.
Copy Chart to Clipboard	(chart only)

Plus the standard grid items – Copy, Copy with Headers, Select All, Filter and Reset Filters.

---

The caveat that matters most

These statistics are emptied by a service restart. The subtitle line always says how long ago that was. A suggestion with enormous benefit on an instance that restarted an hour ago is describing one hour of workload.

They also ignore the indexes a table already has. That is a property of the DMV, not of this report – SQL Server records what it wanted without checking what exists. The Notes column flags the cases where an existing index already leads with the same column, which is the most common reason to dismiss a suggestion.

---

How to read the report

1. Check how long the statistics have been accumulating. The subtitle says. A short window makes every number meaningless.
2. Read the summary line. Concentration tells you whether there are a few real wins or a long tail of noise.
3. Look at the Build first corner of the chart. Large points there are your best candidates.
4. Check Write Ops before committing. The whole point of the chart is that benefit alone is not the decision.
5. Read the Notes column. An existing index leading with the same column often means the real fix is modifying an index rather than adding one.
6. Check Last Wanted. Old suggestions describe queries that may no longer run.
7. Open the advisor for the ones that survive, and read the CREATE INDEX before running it.

Common patterns

One suggestion holding most of the benefit. Usually a genuine win. Check Write Ops and Table Rows, then build it.

Dozens of near-identical suggestions on one table. What consolidation exists for. Switch to Consolidated and you will typically get one index covering all of them.

High benefit, high write ops. The Weigh it quadrant. The index will help reads and cost writes. Measure before committing – on a hot OLTP table the upkeep can outweigh the gain.

Many suggestions with an existing-index note. The table is over-indexed already and the optimizer wants variations. Consider modifying an existing index’s key order or include list instead.

An enormous number of suggestion groups instance-wide. Flagged in the subtitle. Usually an application sending un-parameterised ad hoc SQL, which also bloats the plan cache. Fix that before chasing individual indexes.

Suggestions on msdb. Real and common, particularly on sysjobhistory on an instance with many jobs. The old report hid system databases; this one does not.

---

Where the data comes from

sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats, read instance-wide in one pass using the database_id the DMVs already carry, plus a per-database visit for table context limited to the databases that actually appear in the results.

Nothing is stored. The statistics live in memory and reset with the service.

---

Messages you may see

Nothing found:

There are no missing indexes on this instance. These statistics are emptied by a service restart 21d 14h ago.

Timed out:

The missing index lookup did not finish in time. An instance with a great many databases can take a while to read; try a smaller Top N.

---

Related reports

Report	Why you would go there
Unused Indexes	The other half of the trade – what you can drop to pay for what you add.
Duplicate Indexes	Overlapping indexes that may already cover a suggestion.
Problem Indexes	Broader index health for the instance.
CPU by Query	The queries that would benefit.
Deadlocks by Database	A missing index widens the row range a statement locks.

---

Frequently asked questions

Why does the Impact number differ from other tools? Because it is correctly scaled here. The report this replaced multiplied avg_user_impact without dividing by a hundred and then truncated it to an integer.

Why are master, model and msdb included? Because missing indexes on them are real findings. msdb.dbo.sysjobhistory is a common one on instances with many jobs.

Why is tempdb excluded? Its object ids do not outlive the objects they name, so a suggestion cannot be reliably resolved to a table.

Should I build everything marked Build first? No. It is a starting sort order, not an instruction. Read the Notes column, check Last Wanted, and look at the existing indexes before adding one.

Why do I get so many suggestions for one table? That is what the DMVs return – eight to thirty near-identical variations per table. The Consolidated button merges them into the index that covers them.

Do these suggestions account for indexes I already have? No, and that is a property of SQL Server’s DMVs rather than of this report. The Notes column flags where an existing index already leads with the same column.

Why did all my suggestions disappear? The instance restarted. These statistics live in memory only.

---