June 19, 2018 at 8:00 am
This is a question born out of inexperience with the new 2016 columnstore indexes. I am testing out improvements in querying a very large table in my data warehouse and I have one copy of the table that is rowstore and has a primary clustered index and 4 non-clustered covering indexes. I have another copy of the table with only a clustered columnstore index. When I run the same query against each, I get a suggestion for a non-clustered covering index on the table with the columnstore. I am confused. Why would it suggest a non-clustered index when it has all it needs in the CCI? The tables each have over 191 million records. There are no partitions. Both are on the same filegroup and storage and both are using the same view over their respective tables (the views join to the dimensions and are what most users query against so I wanted to test using what they would use).
If I create the non-clustered index as suggested, is that going to negate using the columnstore? In a non-clustered index on a clustered columnstore indexed table do the pages for the non-clustered index use rowstore or columnstore pointers to the records? In other words, in a non-clustered index against a rowstore table the pages include the location of the record in the primary key. Is there something similar in a non-clustered index over a clustered columnstore index?
BTW, just having the columnstore index improved query time from 1:22 to :33 seconds returning over 3.5 million rows. I know I can improve even more but that is a significant improvement.
Thanks in advance for patience with a relative newby when it comes to tuning.
June 19, 2018 at 1:05 pm
For the CCI, SQL will effectively have to scan all the rows for every query. If you can a nonclus index, SQL may be able to just do lookups to the CCI rows it needs. So basically the same reason a standard table uses a nonclus index.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 20, 2018 at 7:13 am
Interestingly enough, I created the suggested non-clustered index on my columnstore table and ran the same query as before. While the suggestion of the index is gone, the index did not improve performance nor did it even seem to get used in the execution plan! Now I am even more confused.
The CCI did improve performance significantly over the rowstore table: runtime down from 1 min 33 seconds to 30 seconds with data returning almost immediately.
June 20, 2018 at 9:29 am
As with any nonclus index, SQL estimating that too many pages will be needed from the nonclus index causes SQL to drop back to just scanning the main table. I can imagine that this would be even more frequent with CCI tables than standard row tables, but I haven't done/seen any testing yet to confirm that.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply