8

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

4
  • This question is nearly off-topic, as in: "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." Have you considered contacting Microsoft directly via your Technical Account Manager?
    – John K. N.
    Apr 1, 2020 at 13:36
  • Also note that in a partitioned columstore, each partition should have a million or more rows. Apr 1, 2020 at 16:53
  • @Johnakahot2use - completely understand the suggestion here and contacting Microsoft directly is a next step I was considering, but I wanted to ask if there was anything I'd missed that was obvious. There are questions regarding performance and query plans on SE and I think there is value in understanding when a DMV might behave poorly. Also, possibly the question should be titled 'workarounds for poor performance' rather than improving performance - the answer given is valid because I specifically mention workarounds as a solution.
    – ruskind
    Apr 2, 2020 at 16:42
  • 2
    @DavidBrowne-Microsoft - yes, we have data that meets this criteria
    – ruskind
    Apr 2, 2020 at 16:44

1 Answer 1

7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.