March 21, 2018 at 5:46 pm
Comments posted to this topic are about the item Statistics Outdated
May 25, 2018 at 9:36 am
Just wondering what these stats would be used for.
Cos if stats are outdated then that implies they are not being used, since queries would start the process of autorecomputing stats, if it is enabled, and the stats are used in that query.
So this could be a good script in finding stats that need to be dropped.
May 25, 2018 at 1:27 pm
If statistics are out of date, that does NOT mean that they aren't being used, that typically means that the table they are on is large enough that SQL Server's built in rule about only autoupdating statistics after 20% modifications doesn't kick in frequently enough. If a table has 10,000,000 rows in it, autoupdate of statistics won't happen until after 2,000,000 rows have been inserted/updated.
https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/
There were some changes to this behavior introduced in SQL Server 2016:
https://www.brentozar.com/archive/2016/03/changes-to-auto-update-stats-thresholds-in-sql-server-2016/
June 8, 2018 at 7:59 am
I think this is only applicable to SQL Server 2008 R2 and above.
sys.dm_db_stats_properties
is not on my SQL Server 2008 instances
June 8, 2018 at 10:15 am
for older versions, you'd probably have to look at sys.sysindexes and the STATS_DATE function to see out of date stats, maybe something like this:SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS TableSchema, OBJECT_NAME(i.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, i.[name] AS StatisticsName, STATS_DATE(i.OBJECT_ID, i.index_id) AS StatisticsUpdateDate, si.rowmodctr AS RecordsModified, si.rowcnt AS TotalRecords, i.has_filter
FROM sys.indexes i
INNER JOIN sys.objects o ON i.OBJECT_ID=o.OBJECT_ID
INNER JOIN sys.sysindexes si ON i.OBJECT_ID=si.id AND i.index_id=si.indid
WHERE o.TYPE <> 'S'
AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
AND si.rowmodctr > 0
UNION ALL
SELECT OBJECT_SCHEMA_NAME(o.OBJECT_ID) AS TableSchema, OBJECT_NAME(o.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, s.name AS StatisticsName, STATS_DATE(o.OBJECT_ID, s.stats_id) AS statisticsUpdateDate, si.rowmodctr AS RecordsModified, ir.rowcnt AS TotalRecords, s.has_filter
FROM sys.stats s
INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
INNER JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR ON IR.id=o.OBJECT_ID
WHERE o.TYPE <> 'S'
AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL
AND si.rowmodctr > 0
ORDER BY RecordsModified DESC
June 11, 2018 at 6:30 am
Chris Harshman - Friday, June 8, 2018 10:15 AMfor older versions, you'd probably have to look at sys.sysindexes and the STATS_DATE function to see out of date stats, maybe something like this:SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS TableSchema, OBJECT_NAME(i.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, i.[name] AS StatisticsName, STATS_DATE(i.OBJECT_ID, i.index_id) AS StatisticsUpdateDate, si.rowmodctr AS RecordsModified, si.rowcnt AS TotalRecords, i.has_filter
FROM sys.indexes i
INNER JOIN sys.objects o ON i.OBJECT_ID=o.OBJECT_ID
INNER JOIN sys.sysindexes si ON i.OBJECT_ID=si.id AND i.index_id=si.indid
WHERE o.TYPE <> 'S'
AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
AND si.rowmodctr > 0
UNION ALL
SELECT OBJECT_SCHEMA_NAME(o.OBJECT_ID) AS TableSchema, OBJECT_NAME(o.OBJECT_ID) AS TableName, o.type_desc AS ObjectType, s.name AS StatisticsName, STATS_DATE(o.OBJECT_ID, s.stats_id) AS statisticsUpdateDate, si.rowmodctr AS RecordsModified, ir.rowcnt AS TotalRecords, s.has_filter
FROM sys.stats s
INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
INNER JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR ON IR.id=o.OBJECT_ID
WHERE o.TYPE <> 'S'
AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL
AND si.rowmodctr > 0
ORDER BY RecordsModified DESC
Thanks very much for the update. This works fine on my 2008 instance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply