Rajendra Gupta

Columnstore Index Enhancements – online and offline (re)builds

December 13, 2018 by

In this article, we will explore ‘ Clustered columnstore online index build and rebuild’ feature of SQL Server 2019 including comparing execution plans, offline builds and more

SQL Server 2019 is a cross-platform database system that provides support for big data, artificial intelligence, different development languages, and many more exciting features.

We have been exploring SQL Server 2019 in previous articles on SQLShack.

In two previous articles, we explored enhancements in the Columnstore index, specifically.

  1. Columnstore index stats update in clone databases
  2. ‘sp_estimate_data_compression_savings’ Compression Estimates for Columnstore

For this article, we will use the ‘WideWorldStore’ sample database in both the SQL Server 2017 and SQL Server 2019 instance.

In SQL Server 2017 and prior, we needed to build clustered indexes offline. We did not have the option to do this online.

If we try to create a columnstore clustered index, we get the below error message

The statement failed because the ONLINE option is not allowed when creating a columnstore index. Create the columnstore index without specifying the ONLINE option.

It says we cannot use the ONLINE option while creating a columnstore index and we have to create the columnstore index without specifying the ONLINE option.

Let us run the command in SQL Server 2019. Before running, let us verify the instance version.

We can see the current version – Microsoft SQL Server vNext (CTP2.0) – 15.0.1000.34 (X64) .

We also need to verify the database compatibility, it should be 150(SQL Server 2019).


Let us run the ‘create online columnstore clustered index’ command in this database.


Drop the existing columnstore index and run the query again. We can see that the command completed successfully with ONLINE=ON.

Let us try to rebuild the columnstore index online in SQL Server 2017 and SQL Server 2019. In SQL Server 2017, first, create the index without the online option.


This command also fails in SQL Server 2017 with the error message.

ALTER INDEX REBUILD statement failed because the ONLINE option is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying the ONLINE option.

Now we will try rebuilding a clustered columnstore index in SQL Server 2019. In below image, you can see that columnstore clustered index can be rebuilt online as well in SQL Server 2019.

Comparison of the Actual execution plans

Let us view the actual execution plan for the columnstore clustered index activity. In the follow actual execution plan, we can see the operator ‘Online Index Insert’. Save this execution plan into the desired location. We will use this plan later to compare the execution plan.

Below is the actual execution plan if we create the index without ONLINE=ON.

Now we will compare both the execution plans (with ONLINE=ON and With ONLINE=OFF) using the ‘Compare Showplan’.

In ‘Compare Showplan’ give, the path of the same execution plan and it generates the comparison windows.

In the execution plan comparison, you can get the following findings.

You can observe in the above comparison table, that operating cost increases with the online index operation.

Similarly, let us view the comparison for the Insert operator.

In this comparison, the table also, Compile memory, Memory Grant; estimated subtree cost value is more in the online columnstore index operation. This seems to be reasonable though. During online index operations, SQL Server needs to perform extra steps that will obviously take the resources.

Let us do the same actual plan comparison for the rebuild clustered index operation with ONLINE=ON and ONLINE=OFF.

In the below image, you can see the compiled memory and the memory grant is high for the online rebuild operation as well.

Log Size Growth for Online and Offline Columnstore rebuilds

In this section, we will look at the transaction log size for both the online and offline columnstore rebuild activity.

Offline Columnstore Index Rebuild: First, issue a CHECKPOINT command to flush all dirty pages. We will use the sys.dm_db_log_stats dynamic management view to calculate the active log size in MB.

Execute the below query under the database context.


From the below DMV, we can see the below stats during rebuild columnstore clustered index with ONLINE=OFF parameter.

  • Active log Size in MB: 0.2695 MB
  • active_vlf_count: 1

ONLINE Columnstore Index Rebuild: let us run the command with ONLINE=ON.


From the below DMV, we can see that during rebuild columnstore clustered index with ONLINE=ON parameter.

  • Active log Size in MB: 134.875MB
  • active_vlf_count: 11

It is clear from the above comparison that if we rebuild the columnstore index with ONLINE=ON, we require more transaction log space.

Let us perform the same analysis to create the columnstore index as well. We have already created the index, so we need to drop it first before creating it again. Therefore, in the below query, the first statement you can see is DROP INDEX.


For creating a new clustered columnstore index with ONLINE=OFF

  • Active log Size in MB: 0.261718
  • active_vlf_count: 1

Now run the query with ONLINE=ON parameter and observe the log size and vlf count.


  • Active log Size in MB: 145
  • active_vlf_count: 11

Therefore, to create new columnstore clustered index also, more transaction log space will be required.

Conclusion

We have explored the various aspects of creating and rebuilding the columnstore index with online and offline options. This looks promising to me. Explore this into your environment and share your experiences in the comments below.

Table of contents

Columnstore Index Enhancements – Index stats update in clone databases
Columnstore Index Enhancements – data compression, estimates and savings
Columnstore Index Enhancements – online and offline (re)builds

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
119 Views