May 22, 2018 at 4:19 am
Hello,
After migrating to SQL Server 2014, I turned one of our large fact tables (2bn rows) into a columnstore index. This has been brilliant - saved loads of disk space, and significantly increased the time taken to process our OLAP cube.
However... occasionally I need to run a query on the table that just returns yesterday's data. I used to be able to do this in milliseconds by hinting to use a non-clustered index that was sorted by date... BUT, as you know, a columnstore table can't have any non-clustered indexes on it (at least not in SQL Server 2014). As a consequence, the query now takes about 7 minutes.
Does anyone know of any ways I can better optimise a query on a columstore index to focus on a specific column?
May 22, 2018 at 7:48 am
Partition the clus columnstore index on that date. Then SQL can eliminate partitions with non-matching dates. I partition by month on many of our very large tables, but you might use a week or two months or any time frame that leaves you with at least a few million rows in each partition, but not too many.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply