Problem Indexes
Overview
The Problem Indexes report finds indexes that exist but are not doing their job. Three distinct classes, each with a different fix:
| Class | What it means | The fix |
|---|---|---|
| Disabled | Queries cannot use it and writes are not maintaining it. | Rebuild to bring it back, or drop it. |
| Low fill factor | Every page is deliberately written part empty. | Rebuild with fill factor 99 to reclaim the space. |
| Hypothetical | A Database Engine Tuning Advisor leftover – metadata only, never built. | Drop it. |
Zero rows on this report is the good outcome.
The chart draws each index as a bar split into the data it holds and the space the fill factor forces empty, so the size of the problem is visible rather than inferred from a percentage.
Hypothetical indexes are the class that is free to clean up – they are metadata with no pages behind them, left over from someone running the tuning advisor. They cost nothing to drop and nothing keeps them.
What this fixes
The old page was a seven-column grid whose two widest columns were six hundred pixels of raw ALTER INDEX and DROP INDEX text, and whose run actions read that SQL back out of the grid cells to execute it. Beyond that:
- It never looked for hypothetical indexes at instance level, so tuning advisor leftovers were invisible here while the database-level page listed them.
- Its Status column said “Enabled” for most rows on the page. A low fill factor index is enabled; it is just built mostly out of air.
- It skipped master, model and msdb, so a disabled index on
msdb.dbo.sysjobhistorywas something the database-level page would show and this one would not.
Actions now run from the row model rather than by parsing grid text.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Problem 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 Problem Indexes for <server name>.
Requirements
sys.indexes is database scoped and there is no server-wide equivalent, so unlike the missing index rollup this report genuinely has to visit each database. It does that through [db].sys.sp_executesql, skipping anything offline, a snapshot, or unreadable by this login, with each visit in its own TRY so one bad database does not cost the report.
The subtitle line tells you how many databases were actually read, so a partial answer is visible as a partial answer.
The thresholds
| Value | Meaning |
|---|---|
| Fill factor below 70 | Counts as low fill factor. |
| Fill factor 0 | The server default. Not a problem, and not reported. |
| Rebuild fill factor 99 | What the rebuild action and scripts use. |
Fill factor 0 meaning “server default” rather than “zero percent” is the single most common misreading of this setting, which is why the empty-state message says so explicitly.
Reading the chart
Each index is a bar split two ways: the data it actually holds, and the space the fill factor forces empty. For a low fill factor index the empty portion is what a rebuild reclaims.
Rows are ordered by severity: disabled first, then the low fill factors ranked by the empty space a rebuild would reclaim, then hypothetical last. Verdict chips name the class so you are not reading colour alone.
The header lines
The summary line:
34 problem indexes in 6 databases · 3 disabled · 28 low fill factor reclaiming 4.1 GB · 3 hypothetical
The subtitle line explains the three classes in one line each, every time:
disabled = invisible to queries until rebuilt · low fill factor = pages deliberately written part empty · hypothetical = tuning advisor leftovers, never built
It also names how many databases were read, and says showing only disabled when a filter is on.
Reading the grid
| Column | What it is |
|---|---|
| Problem | Disabled, Low fill factor or Hypothetical. |
| Fill Factor | The configured value. |
| Size | Index size, drawn with the same data/empty split the chart uses. |
| Empty Space | What a rebuild would reclaim. Blank for classes where that does not apply. |
| Database | Which database the index is in. |
| Table | The table. |
| Index Name | The index. |
| Type | Clustered, nonclustered and so on. |
| Notes | Context, including whether the index is unique or backs a constraint. |
The raw T-SQL columns are gone. Scripts are on the right-click menu, where they can be read before they are run.
The toolbar
| Button | What it does |
|---|---|
All · Disabled · Low fill factor · Hypothetical |
Narrows chart and grid together, without re-querying. |
Refresh |
Reload now. |
A filter button with no rows behind it disables itself, so the toolbar tells you which classes you have before you click.
Right-click actions
Menu items match the problem. A hypothetical index has nothing real to rebuild, so it is only offered a drop.
| Item | What it does |
|---|---|
| Run Rebuild (fill factor 99) on [index]… | (action) Confirms, then rebuilds. Not offered for hypothetical rows. |
| Copy Rebuild script | The same statement, to read first. |
| Run DROP on [index]… | (action) Confirms, then drops. Only offered when the index is not unique and does not back a primary key – or when it is hypothetical, where there is nothing to lose. |
| Copy DROP script | Always offered. For a unique or constraint-backed index this is the only route, and the script’s comments spell out what dropping it really drops. |
| Copy Rebuild scripts for all N shown | The fix-everything path, grouped by database with a USE per group. |
| Jump to Database Level | Opens the same report scoped to that database. |
The confirmations
Rebuild names the index, the table and the fill factor, and warns about the locks:
Rebuild [IX_Orders_Date] on dbo.Orders with fill factor 99?
… The rebuild holds locks on the table while it runs.
Drop is a warning-icon dialog naming the index and the table.
The rule that a unique or constraint-backed index cannot be dropped from the page is deliberate and inherited from the old report. Dropping a unique index drops a constraint, which is not something that should happen from a context menu.
How to read the report
- Check how many databases were read. A partial visit means a partial answer.
- Deal with hypothetical rows first. They are free – metadata with no pages behind them.
- Then the disabled ones. A disabled index is being paid for in storage and paid for again in confusion, and queries cannot use it.
- Then low fill factor, by reclaimable space. The chart orders them this way already.
- Read the Notes column before dropping anything. Unique and constraint-backed indexes are more than indexes.
- Use Copy Rebuild scripts for all N shown when there are many, and run them in a maintenance window – rebuilds hold locks.
Common patterns
A cluster of hypothetical indexes in one database. Someone ran the Database Engine Tuning Advisor and did not clean up. Safe to drop.
A disabled index nobody remembers disabling. Often disabled for a bulk load that finished months ago. Either rebuild it or drop it, but do not leave it.
Many indexes at fill factor 50 or 60. Usually a maintenance job applying one fill factor everywhere. That is rarely right – fill factor should follow the insert pattern of the individual index.
Fill factor 0 rows absent from the report. Correct. Zero means the server default, not zero percent.
A large low fill factor index with a big empty portion. The clearest win on the page. The bar shows exactly how much storage a rebuild returns.
Messages you may see
Nothing found – the good outcome:
No problem indexes on this instance – nothing disabled, no fill factors below 70, and no tuning advisor leftovers. Fill factor 0 means the server default and is not a problem.
Timed out:
The problem index lookup did not finish in time. This page reads the index catalog of every database on the instance; try Refresh.
Related reports
| Report | Why you would go there |
|---|---|
| Missing Indexes | What to add, alongside what to fix. |
| Unused Indexes | Indexes that work but nobody needs. |
| Duplicate Indexes | Overlapping indexes. |
| Index Fragmentation | A different reason to rebuild. |
Frequently asked questions
Why is fill factor 0 not listed? Because it means “use the server default”, not “zero percent”. The empty-state message says so, because it is the most common misreading of this setting.
What is a hypothetical index? A Database Engine Tuning Advisor leftover. It is metadata describing an index that was never built. It does nothing and costs nothing except confusion, and it should be dropped.
Why can I not drop a unique index from the menu? Because dropping a unique index drops a constraint. You get the script instead, with comments spelling out what it really removes.
Why does the report include master, model and msdb? Because problem indexes in them are real. A disabled index on msdb.dbo.sysjobhistory was invisible on the old instance-level page.
Why is my rebuild taking so long? A rebuild holds locks on the table while it runs, which the confirmation warns about. Run them in a maintenance window.
Is a low fill factor always wrong? No. It is deliberate on indexes with heavy mid-page inserts. The report flags it so you can confirm it was a decision rather than an accident – and shows exactly how much space it is costing.