Product has been added to your cart.

sp_Blitz Column Definitions

sp_BlitzIndex®: by the column.

sp_BlitzIndex® gives you a lot of information about index sanity… but what does it all mean?

Here’s a handy Diagnostic Manual to learn what each column in the results of sp_BlitzIndex® means to you.

[hr]

Sample command to run sp_BlitzIndex®

We’re describing the columns returned by “diagnose” mode of sp_BlitzIndex® here. This is the output you get if you run the procedure with a command like:

[hr]

“Finding” Column

This column is the name of the diagnosis sp_BlitzIndex® is suggesting. Your job is to find out if the diagnosis is correct, and if there’s something you should do about it.

Sample value:
Multiple Index Personalities: Duplicate keys.

[hr]

“URL” Column

This column has a URL that you can use to learn more about your diagnosis. Each set of disorders shares a diagnosis– because what you need to know for all types of duplicate indexes is related.

Sample value:
https://www.brentozar.com/go/duplicateindex

[hr]

“details: schema.table.index(indexid)” Column

We worked hard to make sp_BlitzIndex® return less than 100 columns. It gives a lot of detail and is as compressive as possible. (“Compressive” is an actual word we picked up from SAN documentation. Ridiculous, isn’t it? We thought so, too.)

Because we wanted to keep things compact, the ‘details’ column does triple duty.

1. Identifying Info for the Index

Usually this row contains the schema name, table name, and index name, as well as the index_id of what we’re looking at on that row.

You can tell a lot by an index_id!

  • Index ID = 0: table data when there is NOT a clustered index on the table. (AKA: a “heap”)
  • Index ID = 1: table data in the form of a clustered index. One per table.
  • Index ID >= 2: a non-clustered index — a subset of the data from a table.

Sample value:
Person.Address.ix_Address_City_includes (9)

Decoded:

  • This is table is in the Person schema.
  • The table is named Address.
  • The index is named ix_Address_City_includes.
  • It is index_id 9, a nonclustered index.

2. Aggressive Indexes: Recent Contention Info

Sample value for Aggressive Indexes:
dbo.stock (0): Page lock waits: 528; total duration: 32 minutes; avg duration: 3 seconds;

Decoded:

  • There’s contention on a table named dbo.Stock
  • The contention is happening on the base table of a heap (no clustered index). We know this because (0) denotes index_id=0 — and that’s always a heap!
  • Page level lock waits have been occurring
  • On average when we wait for a page lock wait, we wait for three seconds.
  • We’ve done this 528 since….. how long? (To find out how long this has been tracked in your version of SQL Server, skip on down to the “index_usage_summary” info later in this page. The same rules of the game apply.)

3. Missing Indexes: Recent Cases an Index Could Have Been Used

For more info on this, head on over to the Indexaphobia diagnosis. That contains all sorts of info, including when missing index info is cleared for a given table… and as I’m sure you can guess, it doesn’t follow the exact same rules as everything else.

[hr]

“index_definition” Column

This column breaks down the details of the index as it has been explicitly defined by the user. This is like what you see if you script out the index as a ‘create’ statement in SQL Server Management Studio.

This is NOT necessarily the same as what you’ll see in the output of the built-in sp_helpindex stored procedure. That’s because sp_helpindex leaves out a lot of the story— it doesn’t list things like included columns (and more).

Sample value:
[KEYS] City [INCLUDES] AddressLine1, ModifiedDate, PostalCode

Decoded:

  • This index was defined with one key value: City. That value is at all levels of the index.
  • Three “included” columns were defined for the index. These values will be at least in the leaf level of the index, but they might also be in the key column… that depends on whether the index has secrets. (Check the next column!)
[hr]

“secret_columns” Column

Indexes have secrets. They have to– because indexes need to be able to find every row via a unique set of keys. This means that if you create non-unique clustered and non-unique nonclustered indexes, SQL Server needs to hide away values so that it knows it can uniquely find each row in the tree of those indexes. And even if you were to create all your indexes as UNIQUE, nonclustered indexes need to have a way to locate corresponding rows in the base table, too!

Think of this as a secret favor that SQL Server does for you. A secret favor that can sometimes make your indexes much much bigger than  you think they are.

Sample value:
[KEYS] AddressID

Decoded:

  • In this case the non-clustered index is not unique. (If it was unique, it would not need to sneak any extra columns into its KEY. Instead it could just stow them away in the [INCLUDES].)
  • The table the non-clustered index is on DOES have a unique clustered index defined. (We know this because we do not see a [UNIQUIFIER] that had to sneak into the key.)
  • The key of the clustered index is AddressId.
  • The clustered index key is added to all levels of the non-unique nonclustered index so that it can uniquely find every row when it needs to. It can also use this to look up rows in the clustered index accurately if queries wanna roll that way.

Cheat sheet on common “secret_column” values:

  • [RID]: Row identifier. This is required in Heaps, because there’s no clustered index key. This will appear in the Heap (index_id=0) as well as in any nonclustered indexes defined on the heap.
    • If the nonclustered index is unique, the RID will only be needed as an INCLUDE column (only in the leaf)
    • If the nonclustered index is NOT unique, the RID will be needed as a KEY column (at all levels of the index, upper and leaf)
  • [UNIQUIFIER]: A four byte piece of magic that makes rows in a non-unique clustered index unique.
    • One thing to know about the UNIQUIFIER: it only appears when there are duplicate values. But if you think about it, there’s gotta be some overhead figuring out when it’s needed… because it’s not actually made of unicorns.
    • If the nonclustered index is unique, the UNIQUIFIER will only be needed as an INCLUDE column (only in the leaf). This lets SQL Server connect up to the right row in the clustered index… identified (you got it) UNIQUELY.
    • If the nonclustered index is NOT unique, the UNIQUIFIER will be needed as a KEY column (at all levels of the index, upper and leaf). So it’ll UNIQUIFY everything there as well.

Secrets are fun, but you can see how this gets out of hand.

You can see, because sp_BlitzIndex® does not know how to keep a secret.

[hr]

“index_usage_summary” Column

This column sounds really simple, but it’s just full of gotchas. The operative question here is, “index usage since… when?” The answer is, “it’s complicated.” Don’t worry, we keep the decoder ring right here.

Sample row:
Reads: 27; Writes:0

Decoded:

  • If you’re running SQL Server 2008 R2 or prior, this is index usage since either of the following, whichever is more recent:
    • When the index was created
    • When SQL Server last started
  • If you’re running SQL Server 2012, this is index usage since any of the following– any who knows how recent some of these were???
    • When the index was created
    • When SQL Server last started
    • When the index was rebuilt…. specifically the REBUILD command (not re-organize)

Wait, what? REBUILDS clear index usage info in SQL Server 2012? Say it isn’t so! (Well, it is. If you don’t like this, please vote up this Connect item in the Microsoft bug database.)

We report index usage information because it can be informative, but do make sure you use it with care. Even when you’re quite sure an index hasn’t been used lately, perhaps it’s needed for that once-per-quarter report run by your company CEO?

You do need to trim unneeded indexes, just use caution and the right change management processes to keep things sane.

[hr]

“index_size_summary” Column

These indexes are measured by weight AND by volume. We grab this information out of the sys.dm_db_partition_stats DMV. If a table has more than one partition, we aggregate it up and tell you all about it.

Sample value:
19,614 rows; 1.7MB

[hr]

“more_info” Column

Want to look deeper into this index? sp_BlitzIndex® can have it lean back on the couch and tell you all about its feelings.

This column contains a command that you can run which will give you all sorts of detail on just that index– you can see all the indexes on the table, foreign key relationships, and just missing indexes for that table.

Before finalizing any index changes for a given table, spend some time with the table as a whole. You may change your mind about the right course to make the table more healthy.

Sample value:
EXEC dbo.sp_BlitzIndex @database_name=’AdventureWorks2008R2′, @schema_name= ‘Person’, @table_name=’Address’;

[hr]

“create_tsql” Column

This column is here just to save you some typing. And be warned, this column may not be very exact— because it’s not currently concerned too much about all the options you created this exact index with. After all, this index already exists– you don’t need to create the exact same index!

If you use this column you will need to tweak lots of things: the name, perhaps the column definitions, perhaps adding a drop_existing option or compression. This is really just here so that you don’t have to always start from a blank canvas. But do paint your own picture.

One small luxury item: we check your edition and add in ONLINE=ON if you’re allowed to do that. Because most of the time, you don’t want to take a table offline to create a nonclustered index unless you have to.

Sample value:
CREATE INDEX [ix_Address_City_includes] ON [Person].[Address] ( City ) INCLUDE ( AddressLine1, ModifiedDate, PostalCode) WITH (ONLINE=ON);