Columnstore index question

Question

Columnstore index question

Tac11

SSCertifiable

Points: 6983
More actions
November 12, 2025 at 4:42 pm

#4684288

Hi experts,
I’ve been going through several articles and videos about Columnstore indexes, but I haven’t been able to find clear answers—so I wanted to ask here.
I have a table (a heap, with no nonclustered indexes either) that contains about 110 million records. There’s a job that runs once a week with this query(with all column names instead of *):
SELECT * FROM Inventory ORDER BY Location
The Location column has only around 9,000 distinct values out of 110 million records. I assume this makes it a poor candidate for a nonclustered index, but please correct me if I’m wrong.
I created a nonclustered columnstore index on Location, but it didn’t seem to help with performance. Then I tried a clustered columnstore index (which is for the whole table no column can be selected), which didn’t improve runtime either—but it did reduce storage by more than half.
Could you please suggest what type of index would be most appropriate in this case? Also, is there a script to determine whether this table is a good candidate for a columnstore index?
Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323743 More actions · Answer 1

without a WHERE statement, I think you are looking at pure disk reads as being the slowdown. reading 110 million records, without doing a group by or anything, is going to take time.

did you create the table and indexes with DATA_COMPRESSION=PAGE? that can reduce the reads as more data fits into each page of memory. I've typically gotten tables to shrink 40 to 60%, depending on the content. fewer reads means it'll be faster, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101208 More actions · Answer 2

Absolutely agree with analyzing page compression for the table. It could be a huge help. You can use:

EXEC sys.sp_estimate_data_compression_savings 'dbo', 'table_name', NULL, NULL, 'PAGE'

As you noted, the nonclus index is useless for this, so drop it (if you haven't already).

What you need is a clustered index on Location first. If you have an identity column or other unique column in the table, add that to the end of the index to make it unique. This indexing will eliminate the need for a sort, which will be a huge help.

For the clus index:

/*specify UNIQUE if you can add a unique column; if the value is not inherently unique, for example just Location, leave off UNIQUE, but be aware SQL itself will then be forced to generate a uniquifier value for every row */

CREATE /*UNIQUE*/ CLUSTERED INDEX [index_name] ON dbo.table_name ( Location, Id ) WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 90 /*example value*/, SORT_IN_TEMPDB = ON );

Depending on how rows are inserted to/updated in the table, you may need to reduce the fillfactor on the table when you create the clus index (i.e. leave free space on the pages for new rows to be added later and/or for updating existing rows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Tac11 SSCertifiable Points: 6983 More actions · Answer 3

Thank you Lowell and ScottPletcher, Besides compression and predicate and how can I use columnstore index on this scenario? or should I even use it? Thanks