System databases fail to update user tables statistics, how to workaround ?

Question

System databases fail to update user tables statistics. How to workaround?
I did :
-https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html with

EXECUTE dbo.IndexOptimize
@Databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'N',
@StatisticsSample = 100

-maintenace plan statistics system databases.

And I get last_updated filed outdated.

The script is :

SELECT obj.name, obj.object_id, stat.name, stat.stats_id,
last_updated, modification_counter , OBJ., STAT.
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 type = 'U'
ORDER BY modification_counter DESC

Answer 1

The target version is SQL SERVER 2019

The Ola Hallengren script's

EXECUTE dbo.IndexOptimize
@Databases = 'SYSTEM_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'N',
@StatisticsSample = 100

It doesn't seem to update statistics (user tables) in system databases. The workaround is to run the script :

https://www.dbdelta.com/sql-server-system-table-statistics-update/
USE msdb ; USE Master, and so on.

DECLARE @alenzi nvarchar(MAX) =
(
SELECT
STRING_AGG(
N'UPDATE STATISTICS '
+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(i.object_id))
,';')
FROM sys.indexes AS I
JOIN sys.partitions AS p ON
p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE
OBJECTPROPERTYEX(i.object_id, 'IsSystemTable') = 0
AND i.index_id > 0
AND p.rows > 0
);
EXEC sp_executesql @alenzi ;
GO

Just a few lines. If Ola is correct, you may consider that it isn't good to touch system databases in production. So instead, let SQL servers manage the system databases completely. However, if you find an odd behavior like a very slow DMV that you suspect is related to outdated statistics in system tables, then consider this script.

Answer 2

Hi,
have you tried this parameter in your script:

StatisticsModificationLevel (Specify a percentage of modified rows for when the statistics should be updated)

Maybe there are not enough changes in your statistic, and so it never would do an update for you.

And, if I try to use your script, it would never run. Maybe there are copy & paste error.

Good luck,

Andreas

Answer 3

Very small tables do not need to be updated. This is not a problem.

Answer 4

Hi @Miguel Gavinhos ,

Why did you update the statistics for this table? May be your table do not need to be updated. Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application.

Consider updating statistics for the following conditions:
•Query execution times are slow.
•Insert operations occur on ascending or descending key columns.
•After maintenance operations.

Refer to MS document When to update statistics.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 5

I mean to say ..."...related to outdated statistics in system DATABASES, then consider this script."