October 11, 2017 at 7:43 am
Hi,
I have a Production and Test environment that has Auto-Update Statistics on.
Developers sometimes run sp_updatestats on Test to improve performance.
Is there any need to setup a maintenance job in Production to update statistics? Will this just add additional overhead that isn't required?
There is no performance issues being noted and with auto update statistics on, statistics get updated when required by SQL Server
Please let me know your thoughts
October 11, 2017 at 8:04 am
Whether you need to have regularly scheduled stats updates depends on your data churn. I think the cutoff point for when SQL will trigger an auto-update of stats is something like 10% of the data has changed. So, if you've got a 10 row table, change 1 row and the stats get updated. If you've got a 1000000 row table, 100000 rows will need to change.
Frankly, despite not having a lot of data churn on my servers, I've got twice-weekly stats updates on all user databases, doing a full scan rather than a sampled.
If you're doing regular index rebuilds, I'm fairly certain that also updates the stats as part of the index maintenance.
So, short answer, I'd say do a stats rebuild once or more a week.
October 11, 2017 at 10:14 am
It's actually a 20% threshold for autoupdate of stats, although there's a trace flag that can somewhat improve that behavior. Here's an article that has many subtopics about database statistics:
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/#auto-update
There are a number of options for helping to maintain your statistics, including free scripts such as from:
https://ola.hallengren.com/
You can see what statistics might be out of date by looking at dynamic management views like:SELECT OBJECT_SCHEMA_NAME(s.object_id, DB_ID()) AS SchemaName, OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName, s.name AS StatsName, s.stats_id, sp.last_updated, sp.modification_counter, sp.rows
FROM sys.stats s
LEFT OUTER JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id
OUTER APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE sp.modification_counter >= 500
ORDER BY TableName, s.stats_id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply