Most of you know that you can set SQL to auto update stats. And I’m sure that most of you know that it invalidates stats at about 20%. What that means is that when about 20% of the data in the table has changed, the stats are invalidated. It’s actually 500 + 20% if you wanna be specific. And notice I say they’re invalidated and not updated. Stats aren’t updated when that 20% mark is reached, they’re only invalidated. They’re not rebuilt until they’re needed. Well, for really large tables that can result in stats being oudated longer than they should because the amount of changes needed to invalidate them is much higher. The same issue can occur with smaller tables where the changes are varied enough to throw the distribution out of whack. Some tables are just more sensitive to change than others and that 20% may be too much. You may find that you need to update stats more often… say at 10% or in extreme cases even 5%.
Here’s a sexy little query I put together that will give you the percentage of change an index has had. You can use it to not only run a job to update stats on a different percentage, but you can also just query it and see what percentage your tables are at and how fast they change. It’s an excellent tool for troubleshooting possible stats problems. All the same, it’s pretty handy.
–Get stats percentage of change for tables.
SELECT
so.SCHEMA_ID,
ss.NAME AS [Schema],
OBJECT_NAME(id) AS TableName, si.NAME AS IndexName,
CAST((CAST(rowmodctr AS float)/CAST(rowcnt AS float))*100 AS int) AS Pct,
rowcnt,
STATS_DATE([id], indid) AS [StatsDate]
FROM sys.sysindexes si
INNER JOIN sys.objects so
ON si.id = so.object_id
INNER JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
WHERE rowcnt >= 1000000--500
AND rowmodctr > 0
--AND OBJECT_NAME(id) = 'TableName'
ORDER BY Pct DESC, rowcnt DESC, rowmodctr
Here are a couple notes on the query:
1. Notice there’s a way to query for a specific table.
2. The StatsDate col shows you the last time the stats were updated.
3. I’m also limiting the rowcount to indexes with more than 1mill rows. Feel free to lower that if you like.
4. rowmodctr > 0 is something I threw in there to keep the divide by zero error out of there. This also filters out the system-created stats.
OK, I hope you guys like this one. I personally love it. It’s allowed me to build a process to update stats on some of my tables more aggressively than 20% and keep my server running at its peak.