question

MiguelGavinhos-4453 avatar image
0 Votes"
MiguelGavinhos-4453 asked MiguelGavinhos-4453 commented

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

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiguelGavinhos-4453 avatar image
0 Votes"
MiguelGavinhos-4453 answered MiguelGavinhos-4453 commented

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 @SQL 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 @SQL;
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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
AndreasKreuzberg-1289 avatar image
0 Votes"
AndreasKreuzberg-1289 answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @MiguelGavinhos-4453,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiguelGavinhos-4453 avatar image
0 Votes"
MiguelGavinhos-4453 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.