Sql-server – Improve the poor performance of the sys.dm_db_stats_properties DMV

dmvquery-performancesql serversql-server-2017statistics

We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL column statistics on the partitioned tables.

When running sys.dm_db_stats_properties on these databases, the performance of this table function is extremely poor. We are looking at approximately 1 second per ROW – i.e per 'run' of the this table function.

Here is an example of the query plan generated by a simple query with the CROSS APPLYsyntax used to execute this table function against 1605 stats-table combinations.
Query Plan

There is nothing very helpful here – the performance is clearly poor from the DMV.

My current theory is that due to the nature of the statistics objects in the database, the query against the OPENROWSET internal table is poorly optimized (possibly the TOP 1, and this is what is causing the slowdown.

CREATE FUNCTION sys.dm_db_stats_properties (@object_id int, @stats_id int)
RETURN SELECT TOP 1 -- The first row in the TVF will be the root; avoid scanning entire TVF to find any additional rows.
    object_id, -- Columns now explicit since underlying tvf has additional columns we don't want to expose for backwards compat

However, given that sys.dm_db_stats_properties is a DMV and therefore immutable, we can't change the way it queries the internal tables or anything like that, as far as I know.

The objective here is to obtain the values for the columns rows, rows_sampled, unfiltered_rows, modification_counter, last_updated as obtained from sys.dm_db_stats_properties in a way that doesn't take 3 hours per database! It does not matter if we use a different DMV, so long as the source of the information is not less accurate.

We have tried reorganizing all the system tables to see if that has any effect with statements such as ALTER INDEX ALL ON [Database].sys.[table to reorganize] REORGANIZE. However, no performance increase was observed.

The we are running the following version of SQL server:
Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) – 14.0.3257.3 (X64) on Windows Server 2016.The compatibility level is 140

Best Answer

You can get everything except modification_counter from DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

You'd need to capture its output into a temporary table using something like:

    [Name] sysname NULL,
    Updated datetime NULL,
    [Rows] bigint NULL,
    [Rows Sampled] bigint NULL,
    Steps integer NULL,
    Density float NULL,
    [Average key length] integer NULL,
    [String Index] varchar(3) NULL,
    [Filter Expression] nvarchar(max) NULL,
    [Unfiltered Rows] bigint NULL,
    [Persisted Sample Percent] integer NULL

INSERT #StatHeader

A substitute modification counter is provided by rowmodctr on sys.sysindexes.

This is a workaround, and what we would have done before the new DMV was provided. It's not ideal, and you should report the unexpectedly slow performance you see to Microsoft.