Major Columnstore Performance Enhancement: ALTER INDEX REBUILD WITH (ORDER BY (ColumnA, ColumnB, ...))
Greetings SQL Server Columnstore Team,
SQL Server's columnstore performance is highly dependent upon rowsegment elimination. We have been working with mssql16 and competing product memsql to prototype a financial timeseries tick database. We were able to get mssql16 to outperform memsql, however, it took substantial data re-load engineering on our part that we think would be better made available in the ALTER INDEX REBUILD command.
// sample schema:
CREATE TABLE trades(
In mssql16, we partition this table on the datetime2 field (quarterly partitions). Nearly all of our query patterns specify a SecurityID (or IDs) and a datetime2 range.
Central to our success using mssql16�s columnstore was not only having our data stored in an exact order but also closing/partitioning row segments such that a rowsegment would only have data from 1 financial instrument.
Without doing these things, SQL Server not just noncompetitive with memsql but SQL Server's columnstore query performance was completely unusable for our use case.
We have created T-SQL scripts that for a given PARTITION ID delete and re-insert the data in that partition in �optimized� rowsegment order -- in our case, data sorted by SecurityID then DateTimeKey ... while carefully manipulating the deltastore during the re-load process in a way that minimizes the number of securityIDs that share a rowsegment. This is quite onerous/dangerous and needs to be done for every new table that we have. We also naturally need to re-run these scripts if any data changes in that partition.
All of this can be done neatly in an ALTER INDEX REBUILD command which would greatly enhance the ability of all SQL server users to get vastly better columnstore performance without data reloading/ETL acrobatics.
I look forward to discussing the issue with the team.
Upvotes: 3<=-=Jun 20 2017 11:49AM=-=>
As it happens, I’m in the initial stages of designing just such a feature.
I would very much like to work with you to understand your needs, and see what options would or wouldn’t be helpful to you.
Kevin Farlee firstname.lastname@example.org