Diagnosis: Index-a-phobia

Change can be rough. But if you never change your indexes, that’s even worse.

You have high value missing indexes

sp_BlitzIndex® has diagnosed you as having at least one high value missing index. This is at least one (maybe more) indexes that SQL Server thinks could really speed up queries.

Now, before we get too far into this, let’s start with a warning. Just like advice from people, you don’t want to take SQL Server’s indexing advice TOO literally. You want to take it as a starting point and then decide: is this good advice? What’s the best way I could use this.

Here are some of the gotchas that come with missing index recommendations:

• They’re super specific– and they don’t consider each other. If you follow the missing index recommendations exactly, you’re very likely to create indexes with duplicate keys and /or duplicate include values.
• They don’t consider existing indexes on the tables. There may be an existing index which is just like the missing index, but it needs one added included column. The missing index recommendations will just tell you the exact index that would be perfect for them.
• Sometimes it will recommend an index that already exists.

So, in short, missing index information is super valuable! But it’s not the whole story.

What sp_BlitzIndex® diagnoses

We use a “magic number” to bubble up the most important missing indexes to the top. If you’ve received a diagnosis of Index-a-phobia, at least one index has a “magic number” above 500K.

The magic number consists of these components:

• How often SQL Server thinks it could have used the index
• The average “cost” (expensiveness) of the queries that wanted the index
• The estimated amount that the index would improve those queries

The only purpose of the magic number is to help you sort between indexes that are really useful, and indexes that might help one or two queries, but wouldn’t be worth the cost of maintaining the index.

How to use missing index data

For each missing index, before I make a change plan I like to look closely at all of the indexes on the table. Does the table have a good clustered index?

How many nonclustered indexes are on the table, and what do they consist of? How many other missing indexes are there, and are they like this missing index?

Essentially I need to decide if there is “dead weight” I can remove from the table, or other indexes that can combine.

sp_BlitzIndex® has a feature to help make this process easier. Use the command in the more_info column to get full details on just that table: what indexes it has, their size and usage stats, AND what indexes are missing.

An example

Here’s an example missing index in a test database. The details column reads:

[tpcc22wh].[dbo].[stock] estimated benefit: 9,625,570

In my database named tpcc22wh, I have a table named dbo.stock. There’s a missing index which has a “magic number” estimated benefit over over 9 million.

What does this benefit number mean? To find that out, read the index_usage_summary column:

1381 uses; Impact: 100.0%; Avg query cost: 69.7

Wow, this index seems like it would really help– it’s a very costly query and it would help that query out by 100%!

What would be in the index? This info is in the index_definition column:

EQUALITY: [s_i_id], [s_w_id]

Do I have any options to combine this index?

To find out, I want to look at everything on the stock table. I can do this by copying the command out of the more_info column and running it. In this case, it is:

EXEC dbo.sp_BlitzIndex @DatabaseName=’tpcc22wh’, @SchemaName= ‘dbo’, @TableName=’stock’;

That view shows me interesting things about my case:

• My table has a heap, which is very active. I know this is an OLTP database, so that throws red flags (it qualified for another diagnosis because of this, too.)
• My table has two nonclustered indexes already, both of which are showing 0 reads.
• I don’t have any obvious ways to combine my new index.

This leads me to a decision that I need to look at ALL the indexes on the stock table. I’m going to consider dropping indexes I don’t need, adding a clustered index, and taking care of my missing nonclustered index.

Gotchas

One of the important things to know about missing index data: how long has it been accruing? Once you start index tuning regularly, this can be tricky. Here’s a quick run down.

• Missing index information is cleared on SQL Server restart.
• Missing index info is cleared for a table whenever you modify that table’s metadata– this includes adding or removing columns, or even creating another nonclustered index for the table.
• On SQL Server 2012 and 2014, missing index info (and info in index usage and operational stats DMVs) may be cleared when you rebuild an index as part of index maintenance. (Note: I have not seen this in my tests when using reorganize rather than rebuild.)

Because of these considerations, you need to take into consideration any schema changes you’ve been making and perhaps your index maintenance patterns when tuning indexes.

Index tuning is like doing many things to improve your health: it’s not easy at first

When you first start index tuning it can be very daunting. There are tons of things to learn and it’s quite complicated! But adding the right indexes– and taking the right indexes away– are some of the best things you can do for your SQL Server’s performance.