改善 sys.dm_db_stats_properties DMV 性能不佳的問題

我們有一些數據庫具有COLUMNSTORE壓縮寬表（21 或 30 列）和 2500 個分區（按日期）。該數據庫中大約有 4000 個 stats 對象，其中大部分是分區表上的 INCREMENTAL 列統計資訊。

在這些數據庫上執行sys.dm_db_stats_properties時，這個表函式的性能極差。我們正在查看每行大約 1 秒 - 即此表函式的每次“執行”。

下面是一個簡單查詢生成的查詢計劃範例，該查詢的CROSS APPLY語法用於針對 1605 個統計表組合執行此表函式。

這裡沒有什麼非常有用的 - DMV 的性能顯然很差。

我目前的理論是，由於數據庫中統計對象的性質，對 OPENROWSET 內部表的查詢優化不佳（可能是TOP 1, 這就是導致速度下降的原因。

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)

但是，鑑於這sys.dm_db_stats_properties是一個 DMV，因此是不可變的，據我所知，我們無法更改它查詢內部表或類似內容的方式。

此處的目標是以每個數據庫不需要 3 小時的方式rows, rows_sampled, unfiltered_rows, modification_counter, last_updated獲取從獲取的列的值！sys.dm_db_stats_properties我們是否使用不同的 DMV 並不重要，只要資訊的來源不那麼準確即可。

我們嘗試重新組織所有系統表，看看這是否對諸如ALTER INDEX ALL ON [Database].sys.[table to reorganize] REORGANIZE. 但是，沒有觀察到性能提升。

我們正在執行以下版本的 SQL Server：Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - Windows Server 2016 上的 14.0.3257.3 (X64)。兼容級別為 140

你可以得到一切，除了modification_counter從DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
WITH STAT_HEADER;

您需要使用以下方法將其輸出擷取到臨時表中：

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

rowmodctron提供了一個替代修改計數器sys.sysindexes。

這是一種解決方法，也是我們在提供新的 DMV 之前會做的事情。這並不理想，您應該向 Microsoft 報告您看到的異常緩慢的性能。

引用自：https://dba.stackexchange.com/questions/264058