Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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(
SecurityID int,
DateTimeKey datetime2(7),
Price float,
Size float,
Source char(4),
ConditionCode char(4)
);

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.

17 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base