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

dhamp 1 Reputation point
2020-11-25T15:29:14.803+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,666 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. m 4,271 Reputation points
    2020-11-26T06:49:23.633+00:00

    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.


  2. m 4,271 Reputation points
    2020-11-27T05:36:36.713+00:00

    Hi @dhamp ,

    Is the reply helpful?

    BR,
    Mia


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

    0 comments No comments