Don't you need to specify INCREMENTAL = ON
?
Incremental Statistics Update for specific partition trigger SELECT StatMan for other partitions
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
2 answers
Sort by: Most helpful
-
Erland Sommarskog 112.7K Reputation points MVP
2021-01-04T22:42:52.86+00:00 -
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.