SQL Server 2016: Temporal tables. Indexing. Part I. Rowstore tables

In the previous blog posts, I made an introduction to basic concepts behind temporal tables and schema alteration scenarios. The last part of the series is about indexing and overall query performance and I going to split it to two sub-parts: one to cover initial setup of testing and default scenario – rowstore clustered indexes on base and historical tables. The other part of test is to cover scenarios with columnstore indexes involved.

Foreword

The Microsoft made an important and relevant update in an official documentation for a topic of discussion during this post editing:

An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance. If you create / use your own history table, we strongly recommend that you create such an index that consists of the primary keys from the current table plus the period columns to speed up temporal querying as well as the queries that are part of the data consistency check. If your history table is a rowstore, we recommend a clustered rowstore index. The default history table has a clustered rowstore index created for you. At a minimum, a non-clustered rowstore index is recommended.

As the result, there are couple of scenarios to benchmark:

Current table History table
Scenario 1 clustered rowstore clustered rowstore
Scenario 2 clustered rowstore clustered columnstore
Scenario 3 clustered columnstore clustered columnstore

For each scenario, time to load/update data and query performance measured.

Dataset for testing workloads

For the tests, I going to reuse data generation script used in a previous post:

SELECT TOP 1000000
    ISNULL(p.ProductID + (a.number * 1000),0) AS ProductID,
    p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS Name,
    p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber,
    p.MakeFlag,
    p.FinishedGoodsFlag,
    p.Color,
    p.SafetyStockLevel,
    p.ReorderPoint,
    p.StandardCost,
    p.ListPrice,
    p.Size,
    p.SizeUnitMeasureCode,
    p.WeightUnitMeasureCode,
    p.Weight,
    p.DaysToManufacture,
    p.ProductLine,
    p.Class,
    p.Style,
    p.ProductSubcategoryID,
    p.ProductModelID,
    p.SellStartDate,
    p.SellEndDate,
    p.DiscontinuedDate,
    cast('' as char(1000)) as DataFiller
INTO SQL2016Demo.dbo.Product
FROM AdventureWorks2014.Production.Product AS p
CROSS JOIN master..spt_values AS a
WHERE a.type = 'p'

However, due to necessity of running equal workloads over multiple scenarios, the sets of ProductIDs has to be predefined and preloaded as well as a separate helper tables, so they can be reused:

SELECT TOP 100000 ProductID INTO dbo.Product_SET1
FROM dbo.Product
ORDER BY NEWID()

SELECT TOP 100000 ProductID INTO dbo.Product_SET2
FROM dbo.Product
ORDER BY NEWID()

SELECT TOP 100000 ProductID INTO dbo.Product_SET3
FROM dbo.Product
ORDER BY NEWID()

SELECT TOP 100000 ProductID INTO dbo.Product_SET4
FROM dbo.Product
ORDER BY NEWID()

Scenario 1: Current and history tables with rowstore clustered indexes

This scenario SQL Server Data Engine selects by default. Therefore, primary key (clustered) has to be created on ProductID column with subsequent enabling of the system versioning:

-- duration: 2m 45s
ALTER TABLE dbo.Product ADD CONSTRAINT PK_Product PRIMARY KEY CLUSTERED
(
    ProductID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

-- duration: 1s
BEGIN TRAN
ALTER TABLE dbo.Product
ADD PERIOD FOR SYSTEM_TIME (Valid_From, Valid_Till),
Valid_From datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
Valid_Till datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999.12.31');
ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History, DATA_CONSISTENCY_CHECK = OFF))
COMMIT TRAN

There is no need to create clustered rowstore index on a history table since the right one has been created automatically by “SYSTEM_VERSIONING = ON” command, it is clustered and built on top of primary key of the base table, additionally, it contains system PERIOD values:

EXEC sp_helpindex '[dbo].[Product_History]'

pg3_img1

DML operations performance

First part of benchmark is to measure overall time required for running massive DML operations:

-- 12s
UPDATE  dbo.Product
SET Weight = RAND()
WHERE ProductID in ( SELECT ProductID FROM  dbo.Product_SET1 )

 -- 3s
DELETE FROM  dbo.Product
WHERE ProductID in ( SELECT ProductID FROM  dbo.Product_SET2 )

--7s
UPDATE  dbo.Product
SET Weight = RAND()
WHERE ProductID in ( SELECT ProductID FROM  dbo.Product_SET3 )

-- 3s
DELETE FROM  dbo.Product
WHERE ProductID in ( SELECT ProductID FROM  dbo.Product_SET4 )

In total 25 seconds required to perform operations over 400 000 rows

Performance of the SELECTs

For testing queries of historical data, I will use timestamp of the second operation:

SELECT DISTINCT [Valid_From] FROM dbo.Product_History

--2015-06-15 14:50:38.8230000
--2015-06-15 14:53:53.9592314 -- selected
--2015-06-15 14:54:37.5578868

As in the previous post, for query execution measurement following command to be used:

SET STATISTICS IO, TIME ON

The very first query is non-historic. Simply retrieval of current version of rows with ProductID up to 10000:

-- Table 'Product'. Scan count 5, logical reads 8950, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--SQL Server Execution Times:  CPU time = 30 ms,  elapsed time = 9 ms.
SELECT SUM([StandardCost]) FROM dbo.Product
WHERE ProductID BETWEEN 1 and 100000

The query plan is trivial, however, note that logical reads number is quite high. This is because a “DataFiller” CHAR(1000) column in use:

pg3_img2_query1
Query 1. Execution plan.

The second query perform the same select, however it takes data, which was actual for a moment of the second DML operation: ‘2015-06-15 14:53:53’. This time STATISTICS output reflects IO operations spent on querying data from history table as well. However, the overall impact is quite low – only 3-5% more reads done. When it comes to CPU resources, overhead is comparable also – it adds extra 10% to overall query cost:

--Table 'Product_History'. Scan count 5, logical reads 304, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Product'. Scan count 5, logical reads 8950, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--SQL Server Execution Times:  CPU time = 32 ms,  elapsed time = 11 ms.

SELECT SUM([StandardCost]) FROM dbo.Product
FOR SYSTEM_TIME AS OF '2015-06-15 14:53:53.9592314'
WHERE ProductID BETWEEN 1 AND 100000

Second query. Execution plan
Second query. Execution plan

The next set of queries supposed to trigger clustered index scan, as no indexed columns are involved in the SELECT text:

-- Table 'Product'. Scan count 5, logical reads 182852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 406 ms, elapsed time = 131 ms.
SELECT SUM([StandardCost]) FROM dbo.Product
WHERE Weight > 0.5

The query plan looks trivial again. However, the number of logical reads is even larger and CLUSTERED INDEX SCAN operator can explain that:

Query 3. Execution plan
Query 3. Execution plan

The last query is to repeat logic of the previous SELECT, however, data to retrieve is historical:

-- Table 'Product_History'. Scan count 5, logical reads 6251, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Product'. Scan count 5, logical reads 182852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- SQL Server Execution Times: CPU time = 594 ms, elapsed time = 175 ms.
SELECT SUM([StandardCost]) FROM dbo.Product
FOR SYSTEM_TIME AS OF '2015-06-15 14:53:53.9592314'
WHERE Weight > 0.5

The execution plan looks simple again, both tables: base and historical, scanned so STATISTICS reflects number of page of corresponding objects. The overhead of querying historical data is about 3% according to execution plan. Nevertheless, STATISTICS output shows approximately 30% increase of overall cost of the query:

Query 4. Execution plan
Query 4. Execution plan

Also, I have to mention one weird fact noticed during testing. The fourth query statistics showed very low number of pages scanned of the history table in comparison with the base one.
Simple check of row count showed that objects has different number of records, but the difference  is about 2x, however, the difference in pages is more than 30x times. So, number of expected pages to scan of the history object is something like 85 000, but not a 6 000.
The reason of such “optimization” is in a default behavior of the Data Engine. It creates clustered rowstore index on a history table with a PAGE data compression.

As intermediate conclusion:

  • Overall impact on of the querying current data is zero. Data engine simply perform querying of the original single object and didn’t touch history table
  • Overall impact of the querying historical data is low. In my tests it varying from 3% to 30% and execution plan expands to getting data from two objects: base and history table
  • By default, data engine makes clustered index on a history table compressed using a PAGE type.

In the second part of the blog post, I going to cover impact on performance by using columnstore indexes.