3

We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only).

It has come up with 33 issues, all of which are "Abnormal Psychology: Compressed indexes", and they are all on the temporal tables e.g.

Schema.TableName.ix_IndexName (1). COMPRESSION: Partition 1 uses PAGE [CX] [2 KEYS] SysEndTime {datetime2 8}, SysStartTime {datetime2 8}

I am pretty sure we didn't actively choose to turn compression on for these indexes, and I think it is the default behaviour for system generated history tables. But should I be worried? sp_BlitzIndex thinks I should be...

What are the recommended best practices? Thanks in advance.

Improve this question

1 Answer 1

Reset to default
4

This is default behaviour for temporal tables, the history tables are PAGE compressed by default. This is done out of space & performance considerations, compression will reduce the amount of data that has to be processed, both on disk & memory. This will have a trade-off in extra CPU cycles as SQL Server has to decompress the data. So unless you are under CPU pressure, compression is usually a good thing as your disk IO & memory usage improve.

More information on temporal tables: Temporal Table Considerations and Limitations

For what sp_BlitzIndex page says about it:

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.

So as stated above, this does not mean it is a problem. For the reasons described above.

Improve this answer
3
  • 1
    Right, this is an FYI in this case because before SQL Server 2016 SP1, a compressed index couldn't be restored on Standard Edition. If you are in the small window between temporal tables introduced (2016 RTM) and compression opened to all editions (2016 SP1), you can't back up that database and restore it on a 2016 RTM Standard Edition, for example. This is easy to work around: upgrade the target to SP1 or higher (or remove history, re-create the indexes without compression, and re-enable history). For most other implementations, the warning serves a broader audience (e.g. everyone < 2016).
    – Aaron Bertrand
    Commented Jul 31, 2018 at 12:41
  • 1
    (I wonder if the abnormal psychology warning could be made less prominent if the user is on 2016 RTM and suppressed entirely on 2016 SP1+, especially in the case where it's only found on temporal tables?)
    – Aaron Bertrand
    Commented Jul 31, 2018 at 12:43
  • Also since SQL 2016 SP1 was released more than 12 months ago, SQL 2016 RTM is now out of support.
    – David Browne - Microsoft
    Commented Jul 31, 2018 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.