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.
|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|
|Total time spent|
|Scenario 1: rowstore objects||1694|
|Scenario 2: rowstore current and columnstore history||1665|
|Scenario 3: columnstore objects||573|
|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.
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.