The SQL for retrieving statistics that need updating can return duplicate records.
The current SQL is:
SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';
SELECT DISTINCT ''' + REPLACE(@dbname, CHAR(39), CHAR(95)) + ''' AS [DatabaseName], ''' + CONVERT(VARCHAR(12),@dbid) + ''' AS [databaseID], mst.[object_id] AS objectID, t.name AS schemaName, OBJECT_NAME(mst.[object_id]) AS tableName,
STATS_DATE(mst.[object_id], ss.stats_id) AS last_updated, SUM(p.[rows]) AS [rows], si.rowmodctr AS modification_counter, ss.stats_id, ss.name AS [stat_name], ss.auto_created, ss.user_created, NULL, NULL, NULL, NULL
FROM sys.sysindexes AS si
INNER JOIN sys.objects AS o ON si.id = o.[object_id]
INNER JOIN sys.tables AS mst ON mst.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS t ON t.[schema_id] = mst.[schema_id]
INNER JOIN sys.stats AS ss ON ss.[object_id] = o.[object_id]
INNER JOIN sys.partitions AS p ON p.[object_id] = ss.[object_id]
LEFT JOIN sys.indexes i ON si.id = i.[object_id] AND si.indid = i.index_id
WHERE o.type <> ''S'' AND i.name IS NOT NULL
GROUP BY mst.[object_id], t.name, rowmodctr, ss.stats_id, ss.name, ss.auto_created, ss.user_created
HAVING SUM(p.[rows]) > 0
AND ((SUM(p.[rows]) <= 500 AND rowmodctr >= 500)
OR (SUM(p.[rows]) > 500 AND rowmodctr >= (500 + SUM(p.[rows]) * 0.20)))'
A more complete join and cleaner filter:-
Condition
sys.indexes i => ON ss.object_id = i.[object_id] AND ss.stats_id = i.index_id
sys.partitions AS p => ON p.[object_id] = i.[object_id] AND p.[index_id] = i.index_id
sys.sysindexes AS si => ON si.[id] = ss.[object_id] AND si.indid = ss.stats_id
where o.is_ms_shipped =0
SQL
Here is the full replacement:-
SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + ';'
set @sqlcmd = @sqlcmd
+ '
SELECT
db_name() [DatabaseName], db_id() AS [databaseID]
, ss.[object_id] AS objectID, t.name AS schemaName, [tableName] = o.[name]
, last_updated= STATS_DATE( ss.[object_id], ss.stats_id )
, [rows] = NULLIF(COALESCE( SUM( p.[rows] ), SUM( si.[rowcnt] ), NULL),0)
, modification_counter = si.rowmodctr
, ss.stats_id
, ss.name AS [stat_name]
, ss.auto_created
, ss.user_created
, has_filter = NULL
, filter_definition = NULL
, unfiltered_rows = NULL
, steps = NULL
, [is_disabled] = i.is_disabled
FROM sys.stats AS ss
INNER JOIN sys.objects AS o
ON ss.object_id = o.[object_id]
INNER JOIN sys.schemas AS t
ON t.[schema_id] = o.[schema_id]
LEFT JOIN sys.indexes i
ON ss.object_id = i.[object_id]
AND ss.stats_id = i.index_id
LEFT OUTER JOIN sys.partitions AS p
ON p.[object_id] = i.[object_id]
AND p.[index_id] = i.index_id
LEFT OUTER JOIN sys.sysindexes AS si
ON si.[id] = ss.[object_id]
AND si.indid = ss.stats_id
WHERE o.is_ms_shipped =0
-- dadeniji 2020-11-02 4:34 PM
-- it is possible one wants to discard matches for disabled indexes
and (
( i.is_disabled = 0 )
or ( i.is_disabled is null )
)
GROUP BY
ss.[object_id]
, t.[name]
, o.[name]
, si.rowmodctr
, ss.stats_id
, ss.[name]
, ss.auto_created
, ss.user_created
, i.is_disabled
HAVING
(
(
(
SUM(isNull(p.[rows], 0))
between 0 and 500
)
AND (
si.rowmodctr >= 500
)
)
OR (
(
SUM(isNull(p.[rows], 0) ) > 500
)
AND si.rowmodctr >= (
500
+ SUM
(
isNull(p.[rows], 0)
) * 0.20
)
)
)
'
The SQL for retrieving statistics that need updating can return duplicate records.
The current SQL is:
A more complete join and cleaner filter:-
Condition
SQL
Here is the full replacement:-