Dynamic Management Objects dm_db_stats_properties and dm_db_incremental_stats_properties are slow in SQL Server 2016

Question

I am running into very slow performance of the Dynamic Management Objects dm_db_stats_properties and dm_db_incremental_stats_properties. This problem is specific to a particular set of databases which have the following characteristics.

• There are about ~8000 stats objects in these databases, of which most are INCREMENTAL column statistics on partitioned tables.
• The tables typically have 2500 partitions (by a date column in the data)

My understanding is that the Dynamic Management Objects above rely on the internal DM_DB_STATS_PROPERTIES table function (as identified in the query plan) which doesn't seem to perform well within the tables which have large numbers of incremental statistics.

How can I improve the performance of these objects? Typical queries such as this one take 3-5 hours to complete within these databases, and on other database this takes seconds. This query is taken directly from the docs with trivial additions to ensure that the limitation was not SSMS returning select query results.

   SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter   
   INTO #temptable --adding an insert to prevent SSMS being the limiting factor  
   FROM sys.objects AS obj     
   INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id    
   CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp    
   WHERE modification_counter > 1000;  

We are running Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) on Windows Server 2016.The compatibility level is 140

Answer 1

Hi @dhamp ,

Run code as next:

SELECT s.session_id, s.status, s.login_time, s.host_name,  
        s.program_name, s.host_process_id, s.client_version,   
        s.client_interface_name, s.login_name, s.last_request_start_time,   
        s.last_request_end_time, c.connect_time, c.net_transport,   
        c.net_packet_size, c.client_net_address, r.request_id, r.start_time,   
        r.status, r.command, r.database_id, r.user_id, r.blocking_session_id,   
        r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource,   
        r.open_transaction_count, r.transaction_id, r.percent_complete,   
        r.cpu_time, r.reads, r.writes, r.granted_query_memory   
    FROM sys.dm_exec_requests r   
        RIGHT OUTER JOIN sys.dm_exec_sessions s  
        ON r.session_id = s.session_id  
        RIGHT OUTER JOIN sys.dm_exec_connections c  
        ON s.session_id = c.session_id  
    WHERE  s.is_user_process = 1  

and there is another dmv sys.dm_os_wait_stats, the result can return the total waiting count and waiting time of all waiting states since sqlserver started. From these accumulated values, we can see what sqlserver is waiting for.

Check the result and analyze it or post it here

More information: inf-understanding-and-resolving-sql-server-blocking-problems

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @dhamp ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.