Blog - comments

Hi Stephane,It depends, you can find the stats for MySQL 5.6 below:sysbench 0.5: multi-threaded sys...
Gregory Steulet

Hi Gregory, how many times does it take to prepare the 20M rows ? It seems take so long time...

Overall, from my point of view there is one key issue: There are Oracle installations with standard ...
Hi Guys, I tried for users tablespace and was able to do the below to recover the datafile in pdbs.P...
Harsha C R
Hi Mark, I fully understand your point... With Standard Edition and Standard Edition ONE, if you are...
Gregory Steulet
Blog Stephane Haby SQL Server 2014: Rebuilding Clustered Columnstore Indexes

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

SQL Server 2014: Rebuilding Clustered Columnstore Indexes

I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting "SQL Server 2014 - New Features: xVelocity memory optimized columnstore index" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.

Why do we need to rebuild the Clustered Columnstore Indexes?

When a row is deleted, SQL Server marks the row as logically deleted. Physically, the row is still there.

This is true for the columnstore, but if the row is in the deltastore, the row is directly logically and physically deleted.

In addition to the DELETE query, an UPDATE query is a deletion followed by an insert. 

We can easily consider that a lot of rows are marked as deleted during the lifetime of a database. The next question is: How can we determine when a rebuild must be started?


The traditional question: When and How?

A few specifically interesting Dynamic Management Views (DMVs) are delivered with SQL Server 2014 for Clustered Columnstore Indexes.These views help us understand what is happening in this new feature.

The most important DMV is sys.column_store_row_groups. This view has two interesting columns:

  • total_rows is the total number of rows stored
  • deleted_rows is the total number of rows marked as deleted

You can find more information here.

A comparison between these 2 columns gives us an idea of when to rebuild a Clustered Columnstore Index.

When the number of deleted rows increases and represents a large percentage of the total rows, performance problems can occur on the table.

To reduce the size of the table and of course reduce the disk I/O during a read, the best way is to really delete all rows marked as deleted.

Then, rebuilding the index is required.

The following example will help you understand this problem.


Rebuilding a Clustered Columnstore Index

I have created the Clustered Columnstore Index CCSI_Person_Person on Person's table from the AdventureWorks database:




My first action is an update from a title for all rows with an "e":




10446 rows are updated.

My second action is a delete from same rows, and such as for the update, I have 10446 rows in the column delete_rows:




Afterwards, I change my clause WHERE and I delete all rows with an "a":




18269 rows are marked as deleted.

Now, using a Select command and retrieve a total count of 1703 rows.




With sys.column_store_row_groups and a simple calculation, I have 91% of rows marked as deleted, kind of phantom rows...




If I open the Index properties, no rebuild operation is needed as the index fragmentation is at 0%.

This is good, I do not perform a rebuild because I have fragmentation. 

Columnstore index is not a row-store index type and is stored in memory. Do not forget that!Wink




You can directly rebuild with a right-click on the index:




Or also via T-SQL script: ALTER INDEX CCSI_Person_Person REBUILD PARTITION = ALL

Using my script, I will add two options: ONLINE and DROP_EXISTING




DROP_EXISTING is not possibleEmbarassed!




For ONLINE, same result as for DROP_EXISTING: impossibleFrown




Let's check if my rebuilding really deletes all rows marked as deleted:





As expected all rows previously marked deleted are now physically deleted.



With Clustered Columnstore Indexes, we need to plan Index Rebuild Operation on a regular basis. The best way to do this is to insert this in your index work maintenance plan.

For your information, the rebuilding task in the maintenance plan does not include Clustered Columnstore Indexes rebuilds. Cry

I hope an option will be added in the RTM (Release To Manufacture) version!

Rate this blog entry:

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.



  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Thursday, 30 July 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter