In the latest servicing release for SQL Server 2016, we have modified the DMV
to remove some internal inefficiencies which results into
improved query performance
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
Elapsed time seconds)
Memory grant (MB)
SQL 2016 SP1
SQL 2016 SP1 CU1
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.