SQL Server 2016 – Temporal Tables and Compressed Indexes

sp-blitzindexsql serversql-server-2016temporal-tables

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.

Best Answer

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.

Related Solutions

Sql-server – Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes per customer:

SELECT c.postalcode 
, sum(s.SaleAmount) SaleAmount
, count(postcode_mutations.customerid) as CntCustomerChangedPostCode   
FROM dbo.Sale s
JOIN dbo.Customer c on s.customerid = c.customerid

LEFT JOIN (
SELECT 
    CustomerID
FROM [dbo].[Customer]
FOR SYSTEM_TIME FROM '20140101' TO '20150101'
GROUP BY CustomerID
HAVING COUNT(DISTINCT PostalCode) > 1
) postcode_mutations on s.customerid = postcode_mutations.customerid

WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
GROUP BY c.PostalCode

upd: Since the query is supposed to serve DWH/Analytics scenarios, the columnstore indexing is an option to check. I also made some benchmarks previously for a 10 million rows table.

SQL Server 2016 – Will Temporal Tables Be Included in Standard Edition?

_{Community Wiki answer generated from question comments. Feel free to edit it to improve it.}

Anthony Faull: The Temporal Tables feature is available in all editions of SQL Server 2016 including Standard Edition, Web Edition and Express Edition. It is not limited to Enterprise Edition.

Best Answer

Related Solutions

Sql-server – Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

SQL Server 2016 – Will Temporal Tables Be Included in Standard Edition?

Related Question