Incremental Statistics Update for specific partition trigger SELECT StatMan for other partitions

Yuri Gorelik 1 Reputation point
2021-01-04T14:41:36.057+00:00

Hello

I'm trying to understand the Incremental statistics
When I call

update XYZ WITH RESAMPLE ON PARTITIONS (4)

I see in the profiler that the SQL server is working on many other partitions

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [etl_time] AS [SC0] FROM [dbo].[XYZ] WITH (READUNCOMMITTED) WHERE $PARTITION.fn_computer_header_active = 1219 ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 8)

and many other partitions as well

I'd tried un empty partition and got the same results - very long (days) process

My configuration:
SQL server 2017 CU22 standard edition

Thank you
Yuri

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

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-01-04T22:42:52.86+00:00

    Don't you need to specify INCREMENTAL = ON?


  2. AmeliaGu-MSFT 13,986 Reputation points Microsoft Vendor
    2021-01-05T09:04:23.827+00:00

    Hi anonymous userGorelik-7549,

    Something similar with the following about StatMan in SQL profiler which means SQL server is querying UPDATE STATISTICS:

    SELECT StatMan([SC0])  
    FROM (SELECT TOP 100 PERCENT [---] AS [SC0]  
    FROM [dbo].[tablename]  
    WITH  
    (READUNCOMMITTED)  
    ORDER BY [SC0]) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)  
    

    It seems that this is normal operation on part of SQL Server.
    Please refer to STATSMAN QUERY – KNOWN ISSUE WITH UPDATE STATISTICS and SELECT StatMan which might help.
    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.