Rajendra Gupta
SQL Server 2019 columnstore index execution plan

Columnstore Index Enhancements – Index stats update in clone databases

November 9, 2018 by

SQL Server was launched in 1993 on WinNT and it completed its 25-year anniversary recently. SQL Server has come a long way since its first release. At the same time, Microsoft announced a preview version of SQL Server 2019. SQL Server 2019 provides the ability to extend its support to big data, Apache Spark, Hadoop distributed file system (HDFS) and provides enhancements to database performance, security, new features, and enhancements to SQL Server on Linux.

SQL Server 2012 first introduced Columnstore indexes to improve the workload performance. Using this feature, we store data in the column for the page. We can get high-performance improvements for large-scale queries especially for data warehouse or business intelligence. Columnstore index provides a high degree of data compression too as compared with the uncompressed data.

You can explore the concepts of Columnstore index in these articles

SQL Server 2019 provides following enhancements to columnstore indexes

  1. Columnstore index stats update in clone databases
  2. Compression Estimates for Columnstore
  3. Resumable online index creation

In this series of article on columnstore index enhancements over SQL Server 2019, we will explore Columnstore index stats update in clone databases.

Columnstore index stats update in clone databases.

Before we move further, let us talk little about a newly introduced feature in SQL Server 2014 SP2, DBCC CLONEDATABASE. This command is used to create an empty copy of the online source user database without any data. We can use this feature to analyze or troubleshoot performance issues in the queries related to the query optimizer. This feature creates an internal snapshot of the DB and copies all metadata, schema, and statistics to the new cloned database.

If we combine both the features of Columnstore indexes and Clone database, it does not really start to work well until SQL Server 2017. Let me explain this in SQL Server 2017 first and then we will move to SQL Server 2019 enhancements.

Columnstore index and clone database SQL Server 2017

First, we will prepare the environment with the sample database and load data into it.


Sample DB and table

We’ll load some 2m rows of test data into our database

In the next step, create the clustered columnstore index on this table with the following query.


Columnstore index SQL Server 2019

Let us check the statistics for this table using the sys.stats catalogue view. This view provides information about each statistic in the table, indexes, and views.


Check the stats after creating Columnstore index SQL Server

In the result set, we can see statistics named ‘CCS_Employees’. This statistics is created automatically at the time of columnstore index.

Let us insert 3 million records again into this table and perform some select operation to create the automatic statistics.

Now, if we view the statistics we can one more statistics with the name as _wa_sys*. These statistics are created automatically by SQL Server.

Verify the stats

Let us view the execution plan as well to verify the estimated and actual number of rows in the table. We can see the table is having 5 million rows as we inserted earlier.

view execution plan  Columnstore index SQL Server

Create the clone database using DBCC CLONEDATBASE Command as below.


Clone database for DB having  Columnstore index SQL Server

We have created the database clone and our clone database name is SQLShackDemoColumnSore_CLONE. Just make a note of the highlighted statement that ‘clone database should be used for diagnostics purpose only and is not supported for use in a production environment’.

We can view both the Source and Clone database in SSMS.

View clonedatabase

In the clone database, as shown below, both statistics exist that we checked earlier for the source database.

Verify the stats

In SSMS, we can also compare the statistics for both the databases.

Check stats in SSMS

Now let us run a query against our clone database and in the actual execution plan, we can see that a warning appears ‘Columns with No statistics’.

Also, note the estimated number of rows is 2 million only while our source table contains 5 million rows. It should show the 5 million rows since both the statistics present in the clone database also. It shows that the statistics are not updated for the columnstore index.

Execution plan Columnstore index SQL Server


Executipn plan details

Now let us view this behavior in SQL Server 2019.

SQL Server 2019 Columnstore index and clone database behavior

We observed above that until SQL Server 2017 if we create a clone database for the source database with columnstore index, it does not update the statistics after we create it. This makes difficult to analyze the performance issue if stats are not getting updated.

SQL Server 2019 provides enhancements to columnstore index stats for clone database. Now stats will be updated automatically in the database created using the DBCC CLONEDATABASE.

Let us perform the same test we did in SQL Server 2017. I have copied the data at each stage using Import and Export wizard to keep the data same for both SQL Server 2017 and 2019 versions.

In the below image, we can observe that there is no warning sign if we execute the query in the clone database and the number of records is also 5 million as per the source table. This shows that the stats are updated automatically in SQL Server 2019 for columnstore indexes as well. We can get the same kind of execution plan with all details in SQL Server 2019 as compared to previous versions of SQL Server.

SQL Server 2019 columnstore index execution plan

No warning observed in SQL Server 2019 as we can observe here.

SQL Server 2019 columnstore index execution plan

Conclusion

SQL Server 2019 enhancements for columnstore index stats on the clone database provide a way to troubleshoot performance issues for columnstore indexes as well. Previously, we have had to manually work on the stats update in SQL Server 2017 or prior. We might get more enhancements coming over in next releases related to this. In the next series of article, we will take an overview of a few more feature enhancements of columnstore indexes in SQL Server 2019.

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

Latest posts by Rajendra Gupta (see all)

1,156 Views