Columnstore Index: SQL Server 2016 – Improved DMV performance

SQL product team has made significant improvements in columnstore index functionality, supportability and performance in SQL Server 2016 based on the customer feedback. This blog post focuses on the performance improvements on a DMV done as part of our latest servicing release of SQL Server 2016, SQL Server 2016 SP1 CU1 and SQL Server 2016 RTM CU4. For detailed list of other performance enhancements in columnstore in SQL Server 2016, please refer to  https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-list-of-blogs-on-columnstore-index-published-by-sql-server-product-team/

In the latest servicing release for SQL Server 2016, we have modified the DMV sys.dm_db_column_store_row_group_physical_stats to remove some internal inefficiencies which results into improved query performance and reduced memory grant requirement by the DMV. The reduced memory grant requirement minimizes the interference of querying this DMV on the concurrent user workload running on the server. This DMV is commonly used by DBAs, for example,  to identify index fragmentation or the reason why some of the compressed rowgroups have less than < 1 million rows.

These changes give us around 30% speed up in executing this DMV on a large clustered columnstore index with 100 thousand rowgroups and 10 million column segments.

Select  *  from  sys.dm_db_column_store_row_group_physical_Stats

If we use Showplan comparison tool integrated in SSMS to compare the plan before SQL 2016 SP1 and SQL 2016 SP1 CU1, you will see the following difference in memory grant requirement below.

We highly recommend you to plan, test and apply the latest servicing release of SQL Server 2016 to your production workload to leverage this improvement.

Parikshit Savjani Senior PM, SQL Server Tiger Team Twitter | LinkedIn Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam