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.
- Columnstore index stats update in clone databases
- ‘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).
|
1 2 |
select cmptlevel, name from sys.sysdatabases where name ='WideWorldImporters' |
Let us run the ‘create online columnstore clustered index’ command in this database.
|
1 2 3 4 |
Use SQLShackDemoColumnSore Go CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] WITH (ONLINE = ON) |
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.
|
1 2 3 4 5 6 7 |
CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] Go Use SQLShackDemoColumnSore Go ALTER INDEX [CCS_Employees] ON dbo.Employees REBUILD WITH (ONLINE = ON); |
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.
- This function was introduced in SQL Server 2017. You can get more information about this function in the article.Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs.
Execute the below query under the database context.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CHECKPOINT; ALTER INDEX [CCS_Employees] ON dbo.Employees REBUILD WITH (ONLINE = OFF); SELECT DB_NAME(database_id) AS DBName, recovery_model, total_log_size_mb, active_log_size_mb, active_vlf_count FROM sys.dm_db_log_stats(DB_ID()) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CHECKPOINT; ALTER INDEX [CCS_Employees] ON dbo.Employees REBUILD WITH (ONLINE = ON); SELECT DB_NAME(database_id) AS DBName, recovery_model, total_log_size_mb, active_log_size_mb, active_vlf_count FROM sys.dm_db_log_stats(DB_ID()) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
>DROP INDEX [CCS_Employees] ON [dbo].[Employees] GO CHECKPOINT; CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] WITH (ONLINE = OFF) SELECT DB_NAME(database_id) AS DBName, recovery_model, total_log_size_mb, active_log_size_mb, active_vlf_count FROM sys.dm_db_log_stats(DB_ID()) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
>DROP INDEX [CCS_Employees] ON [dbo].[Employees] GO CHECKPOINT; CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] WITH (ONLINE = ON) SELECT DB_NAME(database_id) AS DBName, recovery_model, total_log_size_mb, active_log_size_mb, active_vlf_count FROM sys.dm_db_log_stats(DB_ID()) |
- 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 |
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023