5

I have a table with almost 1T rows.

create table bigtable (
  K1 int, K2 date, -- PK columns
  C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
  B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
  primary key (K1, K2)
)

I want to use columnstore to increase the query performance. There are the following options.

  1. Create a cluster columnstore index
  2. Create a noncluster columnstore index for all the columns
  3. Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?

The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?

  • Are you using SQL Server 2014? If you are using 2014 your data will have to be static if you want to use Non clustered columnstore. Only the Clustered Columnstore index is updateable in SQL Server 2014 – Stijn Wynants Jun 8 '16 at 7:51
  • Yes, I'm using 2014. I can drop/recreate the nonclustered columnindex periodically in my case because the data is not updated frequently. – u23432534 Jun 8 '16 at 14:27
  • Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly. – Geoff Patterson Jun 9 '16 at 18:22
0

One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).

So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).

|improve this answer|||||
0

Question you need to ask yourself is: do I need to work on single rows? And it seems like you don't. And in that case, just put a Clustered Columnstore Index (your option 1). This will happen:

  • Your table will be compressed
  • Aggregation queries will be faster
  • You don't need to think about which columns your users will use (unlike create a non-clustered index).

It just boils down to: will I have an application looking to read or update only one row, and will it happen often? And if this happens, you can always add a non-clustered rowstore index for that usage.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.