SQL Server 2016: Temporal tables. Indexing. Part III. Enlarged dataset

Temporal Tables is an important and anticipated feature of upcoming SQL Server 2016, it definitely worth series of blogs and articles which I and other bloggers written recently. One of the aspects last weeks covered on this site is a performance. This time I would like to go even further by enlarging 10x times the dataset and provide brief execution results.

Changes in benchmarking

Benchamrks based on the approach used in previous posts, but with some key differences:

  • Number of rows in dbo.Product increased from 1 000 000 to 10 000 000
  • DataFiller column datatype narrowed from CHAR(1000) to CHAR(100)
  • DMLs Insert/Update batch size increased from 100 000 to 1 000 000 rows. Therefore, history table holds rows of two UDDATE batches and two of DELETEs

Queries remained the same as in previous benchmarks. They also available on a github for tests reproduction.

Results

Table 1. Disk space usage per object type in megabytes:

Current table History table Total
Raw data (uncompressed rowstore) 2557 1199 3756
Scenario 1: rowstore objects (default) 2556 453 3009
Scenario 2: rowstore current and columnstore history 2556 100 2656
Scenario 3: columnstore objects 223 98 321

Table 2. Duration of DML operations per scenario in seconds:

Total time spent
Scenario 1: rowstore objects 1694
Scenario 2: rowstore current and columnstore history 1665
Scenario 3: columnstore objects 573

 

 

Table 3. SELECTs performance per scenario in milliseconds:

Query 1 Query 2 Query 3 Query 4
Scenario 1: rowstore objects 30 63 70402 84338
Scenario 2: rowstore current and columnstore history 33 36 68911 70657
Scenario 3: columnstore objects 10 13 3 7

I think, only this part require some comments. The rowstore-based scenarios had a hard time with “query 3” and “query 4”, due to fact that current table cannot be completely cached in a buffer pool and clustered index scan used. Execution time of those queries exceeds one minute, while other queries are still needs milliseconds to return result.

Such difference in execution time make a chart not representative, therefore, the third chart has two frames: the first one based on all values and the second frame has “very slow” query results excluded in order to compare results of other queries.

Conclusions

Temporal Tables benchmarks on enlarged dataset shows that the feature scales extremely well on columnstore based tables. Query execution time measured in milliseconds even after the enlargement of the dataset to ten million rows.

However,  some scenarios, where rowstore objects are in use and table scan performed, shown significant performance drop for cases when dataset cannot reside completely in a buffer pool.