SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Simplified maintenance of columnstore indexes

By Alexander Parakhnevich,

I've just started to use columnstore Indexes in a SQL Server 2016 for a datawarehouse and realized, that it would be nice to have a SP, doing a maintenance of my ccix and ncix, without to know how they called and in wich tables they are :-) After examining of this info: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-defragmentation following scenarios came into my focus -

1) I would like to be able to start a maintenance of a columnstore Index for a table, after data was loaded in it from a SSIS package.

2) I would like to start a maintenance from aMS Job Agent for all ccix in some schema in my database, after all ETLs have been completed.

3) I would like to proceed with a maintanance for all ccix within a database All of this I would like to do in a simple way, just giving names of db, schema or table to a store procedure.

Also I would like to be able make a choice if I do it with a rebuild or with a reorg.

I have solved it for me and I'm glad to share it with you. I belive everyone who is developing for a data warehouse with columnstore Indexes and tries to avoid OPEN row_groups from DeltaStore will need it:-)

Usage examples:

1) Command runs  a reorg for all colunmstores (for only partitions with OPEN RGs) in MyDB.

     exec [dbo].[usp_Maint_ColumnStore] @database='MyDB'

2) Command runs a rebuild for all columnstores (for all partitions in each table) in MyDB

     exec [dbo].[usp_Maint_ColumnStore] @database='MyDB', @Mode='reb', @Partitions='all'

3)Command runs a reorg for all columnstores (in all tables) in MySchema in MyDB

    exec [dbo].[usp_Maint_ColumnStore] @mode='reorg', @database='MyDB', @schema='MySchema'

4) Command runs a rebuild for a columnstore in MyTable existing in MySchema in MyDB with compression 'COLUMNSTORE_ARCHIVE'

   exec [dbo].[usp_Maint_CumnStore] @mode='reb', @database='MyDB', @schema='MySchema', @table='MyTable', @compression='arc'

Total article views: 191 | Views in the last 30 days: 3
Related Articles

Introducing the Columnstore Index

A brief overview of Columnstore index and its usage with an example


Columnstore Indexes: Finally Awesome in SQL 2016

Columnstore indexes are amazing for optimizing the performance of large rowcount queries that would ...


Stairway to Columnstore Indexes Level 12: Clustered or Nonclustered?

The previous levels of this stairway describe details, features, and limitations of columnstore inde...


Columnstore Indexs

Columnstore Indexes for Fast DW The SQL Server 11.0 release (2012) introduces a new data warehouse...


Columnstore Index Changes in SQL Server 2014

SQL Server 2012 introduced columnstore indexes, which can immensely improve the performance of OLAP ...