SQL Server with Mr. Denny

Aug 20 2018   4:00PM GMT

Database Maintenance and ColumnStore Indexes

Denny Cherry Denny Cherry Profile: Denny Cherry

SQL Database

Recently on Twitter I saw a question about what index maintenance you should be doing on your ColumnStore Indexes when it comes to maintenance. As I looked through DCAC’s blog posts I realized that none of us had blogged about this topic before.

The reason for this is pretty basic, there’s really nothing to do when it comes to maintenance on a ColumnStore index.

Normally we do maintenance on indexes to reduce fragmentation, but there’s no fragmentation on ColumnStore indexes as the data isn’t stored on the table in a sort order. Data pages that aren’t needed anymore are removed automatically (the same with dictionary pages). The other big thing that index maintenance does, it update statistics on indexes. Well, there are no statistics on ColumnStore indexes, so there are no statistics to maintain. Why aren’t their statistics? Because every operation is a scan of a ColumnStore index. Since everything is a scan, there’s no need for statistics.


1  Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • GregGalloway
    Denny I think you have the wrong end of the stick here. There is fragmentation: http://www.nikoport.com/2014/07/29/clustered-columnstore-indexes-part-36-maintenance-solutions-for-columnstore/ And though it is a scan, there are still very important decisions the optimizer makes such as what type of join to do when joining other tables. Statistics help the optimizer guess the cardinality coming out of the scan so it can choose a good plan for the rest of the query. See this article particularly the comments. http://www.nikoport.com/2014/09/13/clustered-columnstore-indexes-part-41-statistics/
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: