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.
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.
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
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 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