June 24, 2015 at 12:41 pm
We have two tables with 2.9B and 1.1B rows using clustered column store index. New/changed data is only applied once per week in a published application downtime window.
We ALTER INDEX ... REBUILD on each table to move changes from the deltastores to the actual compressed index. The REBUILD takes 90 minutes and fits well inside our downtime window.
However, a dba (who's not too familiar with columnstores) is insisting on an UPDATE STATISTICS WITH SAMPLE 30 PERCENT, ALL in this window also. It takes 6+ hours, spills into our normal uptime, and affects application performance.
I don't think UPDATE STATS ... ALL is necessary. UPDATE STATS ... INDEX runs instantaneously after the REBUILD and proves that the index stats are up-to-date. Also, I think a clustered columnstore index implicitly has column stats and a manual update is redundant. Lastly, I suspect UPDATE STATS doesn't handle clustered columnstore tables well.
Any opinions on this? Any articles I can reference so we can decide what to do?
June 25, 2015 at 4:56 am
An index rebuild also updates statistics. So no, I wouldn't rebuild those stats right after that. Also, an index rebuild will update the statistics with a full scan, not the sampled approach they're proposing. Unless there's more information here that you're not listing out, I don't recommend that approach at all.
"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
June 25, 2015 at 6:59 am
Agreed with Grant.
Refer following article to do further R&D.
http://blogs.msdn.com/b/psssql/archive/2015/03/06/does-rebuild-index-update-statistics.aspx
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 25, 2015 at 9:28 am
Thanks for the feedback.
The dba referenced the cited MSDN blog and correctly points out that ALTER INDEX ALL REBUILD does not update column stats. My counterpoint is that a clustered columnstore index already has column stats and REBUILD updates every relevant stat.
MS has done a good job of making columnstores compatible with existing SQL commands but they're not conventional tables and best practices for conventional tables may not be applicable to columnstores.
June 25, 2015 at 11:28 am
antonio.collins (6/25/2015)
Thanks for the feedback.The dba referenced the cited MSDN blog and correctly points out that ALTER INDEX ALL REBUILD does not update column stats. My counterpoint is that a clustered columnstore index already has column stats and REBUILD updates every relevant stat.
MS has done a good job of making columnstores compatible with existing SQL commands but they're not conventional tables and best practices for conventional tables may not be applicable to columnstores.
Yeah, statistics created automatically won't get updated by the index rebuild. But, rather than take the full scan that was done on the statistics on your index and turn that into a sampled scan, why not just update the column statistics individually? Sure feels like your DBA is attacking the wrong part of the problem. Most column statistics are probably fine with the automatic stats updates anyway. Plus, most column statistics are probably not the driving force behind the filtering on the majority of your queries, but, for sure, the indexes are (or should be).
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply