sp_BlitzIndex Aggressive Indexes

Diagnosis: your indexes are just plain violent

BAM! BAM! That’s the sound of your indexes clashing into one another.

sp_BlitzIndex® found signs of blocking in your database.

Can’t we all just get along?

Not really. SQL Server runs in pessimistic locking mode by default. Unless you tell it differently, in each database readers may block writers AND writers may block readers.

What does sp_BlitzIndex® diagnose?

sp_BlitzIndex® looks at the sys.dm_index_operational_stats DMV, which contains information about index performance since SQL Server last started up. sp_BlitzIndex® checks for blocking both at the row and page levels (because SQL Server may decide to use either granularity if you don’t boss it around, based on what the query is doing).

We look for three conditions:

Avg lock wait time > 1 second cumulative (row OR page)
Total lock wait time > 5 minutes cumulative (row + page)
More than 10 lock escalation attempts. This means that so many individual lock requests were being issued for the index that SQL Server tried to escalate it up. (Escalation can go to the partition level or table level, depending on what you’ve configured.)

There’s a gotcha here. On SQL Server 2012 & 2014, data from the sys.dm_index_operational_stats may be cleared by simply rebuilding an index during index maintenance (ALTER INDEX REBUILD) command. This may hide blocking from you or make it harder to find. BUMMER. Kendra Little explains that this was fixed in 2012 SP2 CU12, 2012 SP3 CU3, and 2014 SP2. 2016 and newer were never affected.

Reading the details.

If you’re diagnosed with this, check the details column returned by sp_BlitzIndex® for information on how many lock waits have been happening, what type they were, and the total and average duration.

Example output: dbo.stock (0): Page lock waits: 196; total duration: 13 minutes; avg duration: 4 seconds;

Interpretation: These lock waits occurred on a table named dbo.stock, on IndexId 0. IndexId 0 means that this table is a heap– it does not have a clustered index. (A table will have either IndexId 1 for a clustered index OR IndexId 0 if it is a heap. It can never have both.)

There were 196 page lock waits and they took about 4 seconds each. 4 seconds is a very long time in database time!

Diving in

When you see these waits, you should look at the table in more detail. Are there missing indexes that may be causing scans of the clustered index, and that’s where the blocking is?

Check out the more_info column returned by sp_BlitzIndex®.

In our sample case the column reads like this (except it’s all on one line):

EXEC dbo.sp_BlitzIndex
@DatabaseName='StackOverflow',
@SchemaName= 'dbo',
@TableName='Users';

EXEC dbo.sp_BlitzIndex

@DatabaseName='StackOverflow',

@SchemaName= 'dbo',

@TableName='Users';

If we run this statement we will get output from sp_BlitzIndex® with just detail on that individual table. This makes it easy to focus in on index definitions, index usage, and missing indexes.

To learn more about how to reduce blocking with the right indexes, check out: