Index Usage Stats Insanity - the oddities of sys.dm db index usage stats (Dear SQL DBA)

SQL Server’s “index usage stats” dynamic management view is incredibly useful– but does it tell you what you THINK it tells you?

I explain the quirks of how sys.dm_db_index_usage_stats works and why the information is so valuable.

Question: Dear SQL DBA,

Why does the sys.dm_db_index_usage_stats dynamic management view increment the user_updates value even when you have a where clause on a given index that would result in no change to indexed values?

Sincerely,

Going Insane with Index Usage Stats

A quick overview of the “index usage stats” DMV

sys.dm_db_index_usage_stats is a dynamic management view that reports the number of times an index is used by queries for reads or writes.

The “user_updates” column is described in books online as:

Number of updates by user queries. This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count will increment by 1

Source: https://msdn.microsoft.com/en-us/library/ms188755.aspx

There are also columns for:

• user_seeks
• user_scans
• user_lookups

These are all named “user” to indicate that they aren’t behind the scenes system queries, which have their own columns in the view.

“Going Insane with Index Usage Stats” has noticed that the devil is in the details

Books online said, “For example, if you delete 1000 rows in one statement, [the count in the user_updates column] will increment by 1

“Going Insane” noticed that if your delete query deletes zero rows, it will still increment the user_updates column value by 1.

This is totally true, and it’s easy to reproduce like this:

• Create a simple table with an identity column named “i” and make it the clustered index.
• Insert 10 rows with default values, so you have 10 rows, where i goes from 1 to 10
• Query sys.dm_db_index_usage stats for that table, and  you’ll see that user_updates reads as “10”  (the 10 insert statements you ran)
• Run a delete statement against the table where it deletes the row with i = 1000. That doesn’t exist, so it’ll delete 0 rows.
• Look at sys.dm_db_index_usage_stats again– it’ll show 11 for user_updates, even though you didn’t actually delete anything.

It gets weirder: seek operators that don’t get used will update the user_seeks column in index usage stats

Sometimes operators in query plans don’t actually get used at runtime.

For example, the nested loop join operator looks up a value in an “inner” table/index for every row that comes back from the “outer” table/index in the join. If zero rows comes back from the “outer” table/index, the SQL Server will never go and access the “inner” table/index.

But user_seeks will still be incremented by 1, even if the index wasn’t actually used at runtime by that query.

Even more weirdness: user_scans doesn’t necessarily indicate that SQL Server scanned the whole index

When we learn about seeks vs scans, we tend to think of seeks as being super efficient and looking at just a few rows, and scans as reading all the rows in the object.

It turns out this isn’t true. You can have a seek operator that reads all the rows in an index for a query. And you can have a scan operator that reads just a few rows.

Let’s say your query uses a TOP. You may get a scan operator in your plan that feeds into the top. And the query may be so efficient that it quickly finds enough rows to satisfy the TOP requirement, and at that point the scan can just stop.

It may only read a tiny number of pages in a huge index, but still user_scans is incremented by 1.

And finally, rollbacks don’t decrement / undo any values in the index index usage stats DMV

Let’s say you do run a single query that updates 1,000 rows. Like books online says, user_updates will increase by 1.

If you roll back that transaction, the value stays as is. It shows the same value in user_updates as if the transaction committed successfully.

Here’s the secret: “usage” means “this index appeared in a query execution plan”

The misunderstanding here is about what’s doing the “using”. I totally get this, I had the same misunderstanding myself.

Our inclination is to think the DMV answers the question, “Did I use the rows in this index?”

Instead, it answers the question, “Did I run a query with an operator that could do something with this index?”

It’s counting the number of times an operator shows up in a query plan, and categorizing it by type.

It’s not checking if the operator is actually executed on individual runs, if it does a “full” scan, or if the transaction is rolled back.

sys.dm_db_index_usage_stats is still incredibly useful

Although the DMV is higher level than it appears, it still points to the indexes that get used the most

It can also point you to indexes that haven’t been used at all– at least since the last time usage stats were reset for that index

The DMV is really useful for quick, high level insight into what indexes are most popular, and which you should put on a list to monitor, and perhaps eventually drop

Sample uses:

• Reclaiming storage space/ maintenance time: which indexes aren’t used?
• Adding a column or modifying an index: how popular is it? (Quick assessment of risk)

When does index usage stats get reset?

Index usage stats is always reset when a database goes offline (that includes restarting the SQL Server or failing the database over)

Dropping an index or CREATE with DROP_EXISTING will also reset usage stats

In SQL Server 2012, a bug occurred where index rebuilds started resetting index usage stats

The issue is specific to ALTER INDEX REBUILD (not a reorganize command)

That has been fixed in:

• SQL Server 2012 SP3 + CU3
• SQL Server 2014 SP2 (planned as of this recording)
• SQL Server 2016

More detail: /2016/03/07/sql-server-2016-rc0-fixes-index-usage-stats-bug-missing-indexes-still-broken/

Getting back to the question about user_updates - how can we solve that?

If you want specific data about how many writes occur in an index, you have a few options:

1. Tracing with SQLTrace or Extended events: more impact on the instance, but more complete info

2. Looking at sys.dm_exec_query_stats for queries that reference the index: lightweight, but may miss information for queries whose plans aren’t in cache

• Grant Fritchey has a great post on doing this easily by joining to sys.dm_exec_text_query_plan - search for “Grant Querying Plan Cache Simplified” (http://www.scarydba.com/2012/07/02/querying-data-from-the-plan-cache/ )

3. Tracking activity with QueryStore and looking for queries that modify the index: SQL Server 2016 only.

What about Operational Stats?

There is a DMV, sys.dm_db_index_operational_stats that includes more granular information about writes

It’s sensitive to memory pressure, though. Data goes away when metadata for that index is no longer in memory

“How many writes since when?” is super hard to answer with this DMV

If you’re putting in a lot of time on answering the question, you’re better off with sys.dm_exec_query_stats and friends, in my experience

Index tuning can be complex, but it has a great payoff

This is worth your time investment! Indexes are critical to performance.

Stick with it and don’t sweat the small stuff.