July 1, 2015 at 3:45 am
I created columnstore index on the table with 20 columns and about 1000 000 000 rows
every day added about 5M rows
"select" queries became faster because of batch mode and table demand less disk space then before
I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index
server has 128 G RAM
what pitfalls I could face if I will have so many columnstore indexes on one server?
how a could see problems in DMV?
July 1, 2015 at 8:08 am
It's still just about standard monitoring. Keep an eye on your wait statistics, watch to see if these change in a negative fashion. Same thing goes for the queues within SQL Server. Also, to see the maximum benefit from columnstore indexes, you need to see batch processing in the queries. This means that they must cross the cost threshold for parallelism. You may need to adjust this on your server, but it could have implications for other queries, so keeping an eye on cpu usage is going to be important.
By and large, it's all about standard monitoring to ensure that adding or modifying an index doesn't negatively impact the server as well as making sure that you see positive benefits.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2015 at 8:42 am
+1 for what Grant says, and be sure you watch space with the new indexes as well. More indexes will be more space, and more insert/update/delete impact.
July 1, 2015 at 9:16 am
Thanks for your anwers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply