sp_BlitzIndex Abnormal Psych
sp_BlitzIndex® says: We’re Not in Kansas Anymore
Our sp_BlitzIndex® script checks out your indexes and lets you know when it finds some things that are unusual.
Abnormal psychology findings aren’t necessarily problems– just look at this as a big FYI. Some implementations of indexes may mean that the database can only be restored in a certain edition of SQL Server, or they may change performance of some operations. Which apply to you? Check out details on your finding below.
Cascading Updates or Deletes
These sound like a cool shortcut to make referential integrity easier to enforce, but they have an evil dark side. As they work, they use serializable isolation – meaning, only one query at a time is modifying the tables involved. Ouch.
XML Indexes
Your database has XML specific indexes. These indexes can help speed up queries against data stored as XML in SQL Server, but it does come at a price. When you find XML indexes in a database, check how frequently the table is being written to.
You may want to investigate the read patterns for the table in some depth to determine if storing the data as XML with indexes is the best use case— in some situations it is better to store frequently used attributes in separate columns with their own data types and non-XML indexes. Whether this is right for you varies a lot– don’t assume that XML indexes are bad, just check as to whether they are the best option for your data.
One note: sp_BlitzIndex® alerts you to the existence of XML indexes but does not currently give you the definition for these indexes. These can be scripted using SQL Server Management Studio’s Object Explorer feature.
ColumnStore Indexes
sp_BlitzIndex® found one or more ColumnStore indexes! Congratulations, this means you are running SQL Server 2012 or higher, and an Edition which allows Enterprise features to be used. (Cha-ching!) This also means that the database can’t be restored just anywhere: SQL Server Standard edition won’t restore a database using Enterprise features (at least up until 2016 SP1, where some Enterprise features are allowed).
ColumnStore indexes are stored and used very differently than other indexes– data is written on pages by the column and is compressed heavily. These indexes work the best with queries that need a lot of rows from just a few columns in the table (SELECT *’s need not apply).
If your database has ColumnStore indexes, validate that the index is being used frequently by queries and that the table is of a typical size where ColumnStore indexes are useful– this is typically millions or billions or rows.
Spatial Indexes
sp_BlitzIndex® lets you know when you have spatial indexes in the database. This is one of those checks that could just be the tip of the iceberg. If you’re not familiar with these indexes, your mission from here is to find out how they are are used, what queries require them, and what functionality depends on them. These questions may in part be answered by the SQL Server query cache, and may also require talking with the application development team to find out more about the application.
Compressed Indexes (Page or Row Compression)
If sp_BlitzIndex® finds page or row compressed indexes, it also lets you know what type of compression it found. To see which type, look at the details column.
Sample row:
Sales.SalesPerson.PK_SalesPerson_BusinessEntityID (1). COMPRESSION: ROW
If the index is partitioned, sp_BlitzIndex® will list whether each partition is compressed in the same column.
Sample row:
dbo.OrdersDaily.NCOrderIdOrdersDaily (2). COMPRESSION: NONE, PAGE, NONE, NONE
Data compression is an Enterprise edition only feature (at least up until 2016 SP1, where some Enterprise features are allowed). That means if you find compressed indexes, the database can only be restored to limited editions of SQL Server (not including Standard Edition).
While data compression can save space for data on disk and in memory, using it requires SQL Server to use more CPU when reading and writing data, and should be used with care.
Partitioned Tables/ Partitioned Indexes
Partitioned indexes are an Enterprise edition only feature (until 2016 SP1) — databases using them can’t be restored just anywhere! They also change many decisions the query processor makes about how to use indexes, and can allow you to load and remove large amounts of data from a table very quickly.
When you find partitioned indexes in your database, you want to first learn what has been selected as the partitioning key. This is a single column in the table used to divide the index into partitions. To find this, check out the index_definition column.
Sample row:
[PARTITIONED BY: OrderDate][CX] [PK] [KEYS] OrderDate, OrderId
In this case, the table has been partitioned by OrderDate, and this unique clustered index (which is also the primary key) contains two columns: OrderDate and OrderID. Understanding the partitioning key is critical to understanding query performance for a partitioned table. The optimizer will attempt to perform “partition elimintation” whenever possible using the partitioning key.
Partitioning is a complex subject and you should also familiarize yourself with the partitioning function, partitioning scheme, and data loading process for the table.
Non-Aligned Partitioned Indexes
One of the many gotchas with partitioned tables is partitioned indexes aren’t great for every query. When you have a partitioned clustered index on a table, you still have the option to create what are called “non-aligned” nonclustered indexes on the table– these are nonclustered indexes which are NOT partitioned, but exist on a table which is either a partitioned heap or a partitioned clustered index.
Non-aligned nonclustered indexes can be great for query performance, but they take away one of the biggest features of table partitioning. When they exist, you can no longer perform “partition switching” on the table.
If you find non-aligned nonclustered indexes on a partitioned table, find out how much the index is being used, how it’s being used, and if partition switching is needed or useful.