Improve the poor performance of the sys.dm_db_stats_properties DMV

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.

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)
RETURNS TABLE
AS
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
    stats_id,
    last_updated,
    rows,
    rows_sampled,
    steps,
    unfiltered_rows,
    modification_counter,
    persisted_sample_percent
FROM OPENROWSET(TABLE DM_DB_STATS_PROPERTIES, @object_id, @stats_id)

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 i

You can get everything except modification_counter from DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
WITH STAT_HEADER;

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

CREATE TABLE #StatHeader
(
    [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
EXECUTE('DBCC SHOW_STATISTICS (table, stat) WITH STAT_HEADER;');

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.

StackExchange Database Administrators Q#264058, answer score: 7