Transcript Tracking Index Usage in SQL 2012 and 2014

Tracking Index Usage in
SQL 2012 and 2014
Obligatory “Who Am I” slide

Ken Schwartz

Doing Database-ish stuff for 15+ years

Data Architect at CommerceHub

Particular areas of interest are query tuning and index tuning

Twitter: nope, I don’t tweet

Blog: nope, I don’t blog

Facebook: nope, not that either

Feel free to email me: [email protected]
Goal: Develop a way to track index
usage over a period of time.

Typical question for DBA to address: What indexes do we have that we
don’t really need?

Obvious answer: Any index that we update, but never read from

Fuzzier answer: Any index that we update WAAAAAY more than we
read from
 How

much more is “WAAAAAY” more?
To answer, we need to find information on index usage. This is easy –
do a quick AltaVista search

Hmm…AltaVista no longer exists

Now what do we do?

Lycos search!!!
sys.dm_db_index_usage_stats

DMV to track index usage supplied by Microsoft. THANKS!

Here’s the code I turn to…I stole it from the internet and
tweaked it about 5 years ago
 Just
run this in whatever database you want to see
index usage

For ease of use, I turned this into a proc, “ix_rw_ratio”
(index_read_write_ratio) – just pass in name of database
you want info for – can run from any database

Hmmm…not very exciting. Well, server just restarted
when I booted up, so DMV’s were reset – because that’s
how DMV’s work
sys.dm_db_index_usage_stats
(continued)

Let’s run some queries and recheck the DMV info

Run some for scans, some for seeks

And now recheck

OK…so everything is exactly as we expected

Why are you making me watch this, Ken?
Accumulating Index Usage Stats

OK, finally…the point of this all…

Easy enough:
 Create
a table to log index usage
 Create
a proc that hits the DMV, and for each index,
upsert the usage in the log table
 Create
SQL Agent Job that calls the proc
 Schedule
it to run every…day…week…whatever
 Except…ummm…has
last run?
there been a server restart since
sys.dm_os_sys_info





Microsoft to the rescue again. This DMV gives us exactly
what we need
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
Now make sure your proc checks if there’s been a server
restart since last run and if so, create a new record for all
indexes (if not, just update existing records)
When querying the logging table, aggregate as needed
So far, really nothing new – this is pretty much the classic
solution
I repeat myself when under stress…

OK…so everything is exactly as we expected

Why are you making me watch this, Ken?

Let’s look at one more example now…look at what
happens after we run some index maintenance (which we
all do, right? Well, that’s a whole ‘nother debate there)
Whaaaa????

What happened to the index usage info?

“That never happened before” – at least, not prior to
2012

What does BOL say? From
https://msdn.microsoft.com/enus/library/ms188755.aspx
(2014 page on
sys.index_usage_stats): “The counters are initialized to
empty whenever the SQL Server (MSSQLSERVER) service is
started. In addition, whenever a database is detached or
is shut down (for example, because AUTO_CLOSE is set to
ON), all rows associated with the database are removed.”

Same exact entry for 2012
Quick! Someone tell Microsoft so they
can fix this bug

First reported 4/27/2012 on connect.microsoft.com by
Joe Sack (sql skills)

91 to 0 votes to fix

So, they must working pretty hard to take care of this,
right?

“Status: Closed as won’t fix”
So…back to index usage logging

Our index maintenance job is set to do an index rebuild when
fragmentation hits a certain level (20%? 30%?...whatever)

As soon as that happens, we have lost all our DMV numbers for
that index. Just like there was a restart, except...


Harder to track, since it is index by index

Harder to track, since there is no internal info tracking when
an index was rebuilt (or, if there is, I couldn’t find it)
Now a heavily used index – especially if on a table with a lot of
churn – can look like it was hardly ever used. So we risk
dropping exactly the indexes we need most.

Yay!
High Level Steps for the Fix (not “The Fixx”)

Make sure your periodic index maintenance rebuild job logs its actions

Create a table to hold this info

Create a table to hold index usage stats

Create a staging table to hold stats since last time the stats were
gathered (i.e., delta)

The proc that runs as part of the maintenance job has to update usage
info

The proc will run BEFORE the index maintenance step

The proc will capture the usage delta for each index

For each delta, add to the appropriate record, except…

If we’ve had a restart, just add a new record for each index
(just like old style)
A picture is worth 1000 words
SQL Agent Job

Step 1: Track Index Usage

Step 2: Index Maintenance (Skip/Reorg/Rebuild)
A picture is worth 1000 words

Step 1: Track Index Usage

SQL Agent Job
Staging Table Swaps Old Data For New Data
Pre
IX1

During
Prior
Current
145
151
IX1


Prior
Current
151
???
IX1
Prior
Current
151
172
Unless there was a rebuild of index 1 since last time
Pre
IX1
After
During
Prior
Current
145
151
IX1
After
Prior
Current
0
???
Either way, the delta (= usage since last time) = 21
Step 2: Index Maintenance (Skip/Reorg/Rebuild)
IX1
Prior
Current
0
21
More Details…the tables
CREATE TABLE dbo.index_maintenance_log
 This is updated as part of the job that actually does the
rebuild/reorganize


action_taken is one of skip/rebuild/reorganize

maintenance_job_time is when the nightly/weekly/etc job
started

start_date is when the rebuild/reorganize started

end_date is when the rebuild/reorganize completed
The Tables (continued)

CREATE TABLE [dbo].[index_usage_stats]

This holds the info on usage – the info we use to evaluate
whether or not to drop indexes

Pretty straightforward – just capture everything the DMV’s
offer
Additionally, two more columns:


Start_time (which hold when the server last restarted)

Capture_time (which is when the records was
created/updated)
The Tables (continued)

CREATE TABLE [dbo].[index_usage_stats_staging]

The “_user” and “_system” columns all get defaults of 0

As the “staging” implies, this is where we hold info on the
usage, from last run until this run

For a given index, we will either add the usage number, or
add a new row…more later

The table is not truncated/refilled with each run, but
instead the values get over-written each run
So, what does the proc do – a bit more detail?

Make sure that the staging table has rows for all indexes/heaps that have
usage

Add records to the usage stats table for any index that had no previous usage
(including new indexes)

Has the server been restarted since our last capture?

Yes: Add a new record for every index that has had any usage since
restart – pulling the info directly from the DMV
(dm_db_index_usage_stats)

No:

Reset staging table’s “prior_run” numbers to its “current_run”
numbers

For any indexes that have been rebuilt since our last run, over-write
the new “prior_run” values with “0”

Set the “current_run” numbers to those from DMV

Update usage stats table by getting the delta from the staging table,
and add the delta to the existing value
So, let’s see it work…

OK, let’s load up some demos here. Hang on to your hats.
What does the proc look like? Let’s see
the code already…

Ok. Here it is…
Questions?
OK…now my turn

This presentation made frequent use of sys.dm_db_index_usage_stats. But
that is not the only stats-related DMV. There is also
sys.dm_db_index_operational_stats

Can anyone tell me the difference between them?

Usage_stats tells us how many times an index shows up in the execution plan of
code that gets executed

Operational_stats tell us how many times operations on the index were executed

So, for example, suppose we have an index that shows up in a query plan as
the inner part of a loop join. In usage stats, the number will bump up by 1
when the SQL is run

But in operational stats, the number will increment by the amount of
iterations in the loop.

There is one catch…umm…one cache???