Workaholic Indexes

These poor indexes. Do they ever get to rest?

Checking for top tables

We have two checks for workaholics. In sp_BlitzIndex® output, they’ll look something like this:

Let’s break down what that means.

Workaholics: Scan-a-lots (sys.dm_db_index_usage_stats)

These indexes show as having the top user scans according to the sys.dm_db_index_usage_stats DMV. Don’t panic — scans aren’t necessarily scanning the full table. They could be doing a partial scan of the table.

This DMV tells you how many times a query used an index in its execution plan– but it doesn’t tell you exactly how many times the index was accessed. Only the number of operators referencing it when the plan was run.

A simple example: I run a query that does a nested loop lookup to retrieve a value from the index PK_Address_AddressID. The query runs once, and in that one run it executes the lookup 30 times. This is counted as one “use” of PK_Address_AddressID, even though 30 lookups were executed.

If a single index appears more than once in an execution plan (due to a subquery, union, etc), then it’ll be counted once for each operator in the plan.

TLDR; index_usage_stats does NOT show you the number of “reads” or “writes” an index is really serving up.

This check takes the number of scans and multiplies it by the size of the index to get the “Scan factor”, then sorts by that. This number isn’t important, we’re just trying to identify your largest, most accessed indexes.

Data persists in this DMV:

• Since the database has been brought online (SQL Server 2005-2008R2)
• Since the database has been brought online OR since the last rebuild (SQL Server 2012 and 2014)

Workaholics: Top recent accesses (sys.dm_db_index_operational_stats)

This check is a bit different– and we’re checking a different DMV. The sys.dm_db_index_operational_stats DMV tracks the count of recent index accesses.

Unlike index_usage_stats, this DMV checks how many times an index is actually being hit. If you have a nested loop lookup that runs 30 times in one plan, you’ll get a count of 30 for the related metric in index_operational_stats.

One key difference is that data doesn’t last as long in this DMV. Whenever metadata for an index is removed from memory, the counts get reset for that index.

In other words, this is a best guess at your most recently busy indexes– for things that are still in memory. But it’s not perfect.

Don’t worry too much about the numbers

When you look at this diagnosis the main thing to ask yourself is, “Does it make sense that this would be one of my most frequently accessed indexes?”

If not, you may have queries using resources that you’re not aware of. Could be time to take a dive into your execution plan cache.