Here's our normal stats maint (separate from index maint).
EXECUTE
dba_utils.dbo.IndexOptimize
@Databases = 'eif_cde',
@FragmentationLow = NULL,
@FragmentationMedium =
NULL,
@FragmentationHigh =
NULL,
@UpdateStatistics =
'ALL',
@OnlyModifiedStatistics =
'Y'
Step 1: run the code from the previous blog post, making sure it doesn't drop the table when done.
Step 2: run the below code, substituting your own windows. In my case, I wanted to see which newly updated stats may have fixed the issue.
--and now to return which particular stats hadn't been updated.
SELECT stats.the_schema_name,
stats.table_name,
stats.stat_name,
stats.updated,
-- stats.[inserts since last update],
-- stats.[deletes since last update],
columns.name FROM #stats_info2 stats
INNER JOIN sys.STATS ss ON ss.NAME = STATS.stat_name
INNER JOIN sys.stats_columns sstatc ON ss.object_id = sstatc.object_id AND sstatc.stats_id = ss.stats_id
INNER JOIN sys.columns ON columns.column_id = sstatc.column_id AND columns.object_id = ss.object_id
WHERE stats.updated >='20180413 14:00'
AND NOT EXISTS
( SELECT 1 FROM #stats_info2 stats2 WHERE
stats2.updated < '20180413 14:00' --AND stats2.UPDATEd >'20180412 22:59:00' but gives the same results
AND STATS.the_schema_name = stats2.the_schema_name
AND stats.table_name =stats2.table_name
AND stats.stat_name = stats2.stat_name
)
ORDER BY the_schema_name, table_name, stat_name
No comments:
Post a Comment