Blog - comments

Hi Christopher, It's there. I't not an option that you check at install. Just use it by setting inme...
Hello, Thanks for the nice blog. I tried the latest 12c download available in Oracle's website and I...
Christopher Bernard
-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Hey...I think you forgot that Hotspot have a JIT compiler too. The difference is in the time wherer ...
Anderson

Thanks for the content..

vani
Blog Stephane Haby SQL Server 2014: Rebuilding Clustered Columnstore Indexes

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, 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 the 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:

 

 b2ap3_thumbnail_Rebuild01.jpg

 

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

 

b2ap3_thumbnail_Rebuild02.jpg

 

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:

 

 b2ap3_thumbnail_Rebuild04.jpg

 

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

 

b2ap3_thumbnail_Rebuild05.jpg

 

18269 rows are marked as deleted.

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

 

b2ap3_thumbnail_Rebuild06.jpg

 

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

 

b2ap3_thumbnail_Rebuild07.jpg

 

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

 

b2ap3_thumbnail_Rebuild03.jpg

 

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

 

b2ap3_thumbnail_Rebuild08.jpg

 

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

 

b2ap3_thumbnail_Rebuild09.jpg

 

DROP_EXISTING is not possibleEmbarassed!

 

b2ap3_thumbnail_Rebuild10.jpg

 

For ONLINE, same result as for DROP_EXISTING: impossibleFrown

 

 b2ap3_thumbnail_Rebuild11.jpg

 

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

 

 b2ap3_thumbnail_Rebuild12.jpg

 

 

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

 

Conclusion

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:
3

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.

Comments

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

Leave your comment

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

Contact

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