Adding New Indexes: Where Do You Start?
If you’ve ever looked at a slow SQL Server system and thought, “We probably need better indexes,” you’re not alone.
The hard part usually isn’t knowing that indexing matters. It’s knowing where to start.
Most environments have hundreds (or thousands) of tables. Many already have indexes. Some have too many. And if you start guessing, you can easily end up with indexes that don’t help, don’t get used, or worse, slow down writes and inflate storage.
That’s why Database Health Monitor includes the Enterprise Index Review Report. It gives you a clear, structured starting point for improving performance with indexes, without turning indexing into a guessing game.
How to Access the Enterprise Index Review Report
You can open the report by following this path in the Server Tree:
Instance -> Database -> Real Time -> Indexing -> Enterprise Index Review Report
What the Enterprise Index Review Report Shows You
The Enterprise Index Review Report is designed to answer a few key questions:
1. What indexes already exist?
Before you add anything, you need to see what’s already there.
The report makes it easy to review current indexes so you don’t accidentally:
- duplicate an index
- add something redundant
- add an index that’s almost identical to one already present
2. What indexes is SQL Server recommending?
The report also includes index recommendations pulled from SQL Server’s internal missing index recommendations.
This means the report is not based on random guesses. These recommendations come directly from SQL Server’s observed workload.
3. Which recommendations are the highest priority?
Not all index recommendations are equal.
Some can improve performance dramatically. Others may barely move the needle.
This report helps you prioritize.
A Key Detail: Recommendations Reset When SQL Server Restarts
SQL Server’s missing index recommendations are stored in memory and in DMVs (dynamic management views).
That means:
If SQL Server restarts, those recommendations start over.
So when you look at this report, keep the context in mind:
- It reflects workload since the last restart
- It reflects what SQL Server has observed recently
- It may not represent seasonal, weekly, or month-end workloads unless the instance has been running through those periods
This is important to keep in mind, especially if you know your environment has workloads that run infrequently (such as month-end jobs, weekly reporting processes, or one-off maintenance tasks). Those workloads may not appear in the recommendations if they have not run since the last restart.
What Does the “Cost” Number Mean?
One of the most important aspects to look at in the report is the recommendation cost.
You might see a recommendation with a number like:
Cost: 1252798
So what does that mean?
The simple explanation:
That number is SQL Server’s estimate of how beneficial the index could be.
In most cases, it’s derived from the missing index DMVs, which calculate a score based on things like:
- how expensive the queries are
- how often those queries run
- how much improvement SQL Server believes the index could provide
The practical interpretation:
A higher cost usually means:
- the missing index affects a more expensive workload, and/or
- it’s being triggered frequently, and/or
- SQL Server expects a meaningful performance gain if the index exists
So if you see:
Cost 1252798
and another recommendation:
Cost 1290
The 1252798 recommendation is typically a much better candidate to investigate first.
What the cost is NOT
This is important:
- It is not the exact number of milliseconds saved
- It is not a guaranteed performance improvement
- It is not a “go add this index immediately” score
It’s best thought of as a prioritization tool.
The Right Way to Add Indexes: Slow and Controlled
Indexes are one of the most powerful performance tools in SQL Server, but they come with trade-offs.
So even if a recommendation looks great, we strongly recommend the following:
Add indexes slowly
Don’t add 20 indexes in one day.
Instead, add one (or a few), validate, monitor, and then move to the next.
Add them during off-hours
Creating an index can increase IO, use CPU, consume memory, and block or slow down activity (depending on edition, settings, and index build type).
It’s often best to plan index changes during:
- low activity windows
- off-hours
- maintenance windows
Validate the index is actually being used
After adding an index, you want to confirm it’s helping.
That means checking:
- Is the index being used for seeks/scans?
- Did query duration improve?
- Did CPU decrease?
- Did reads decrease?
If an index isn’t being used, it may not belong in your system long-term.
Not Every Recommendation Should Be Added
SQL Server will often recommend indexes aggressively.
If you blindly apply every recommendation, you can create a different problem.
Index bloat
Too many indexes can cause any number of the following issues:
- slower INSERT/UPDATE/DELETE operations
- increased write latency
- longer maintenance windows (rebuild/reorg)
- larger backups
- increased storage use
- more memory pressure (buffer pool)
In other words:
Adding indexes can speed up reads, but too many indexes can slow down everything else.
That’s why the Enterprise Index Review Report is best used as a guide, not a script to blindly execute.
The Report Gives You a Starting Point, Not a Guessing Game
When you know performance needs help, indexing can be a great starting point for relieving pain.
But indexing blindly can easily turn into trial and error, wasted time, unpredictable results, or even worse performance than what you started with.
If you are unsure where to start on your indexing, or even if you just want to review your current indexes, Database Health Monitor‘s Enterprise Index Review Report is here to help.
