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
- Columnstore Index in SQL Server
- Columnstore Indexes to improve your Data Warehouse Staging Environment
- Create a Clustered Columnstore Index on a Memory-Optimized Table
SQL Server 2019 provides following enhancements to columnstore indexes
- Columnstore index stats update in clone databases
- Compression Estimates for Columnstore
- 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.
|
1 2 3 4 5 6 7 8 9 |
Create Database SQLShackDemoColumnSore GO Use SQLShackDemoColumnSore GO CREATE TABLE [dbo].[Employees]( [EmpID] [int] NOT NULL, [EmpName] [varchar](50) NOT NULL, ) Go |
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.
|
1 2 3 |
Use SQLShackDemoColumnSore Go CREATE CLUSTERED COLUMNSTORE INDEX [CCS_Employees] ON [dbo]. [Employees] |
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.
|
1 2 |
select * from sys.stats where object_id=OBJECT_ID('employees') go |
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.
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.
Create the clone database using DBCC CLONEDATBASE Command as below.
|
1 |
DBCC CLONEDATABASE (SQLShackDemoColumnSore, SQLShackDemoColumnSore_CLONE); |
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.
In the clone database, as shown below, both statistics exist that we checked earlier for the source database.
In SSMS, we can also compare the statistics for both the databases.
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.
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.
No warning observed in SQL Server 2019 as we can observe here.
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 |
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
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023