How often should we use Update Statistcs.(More frequent or Not)

Question

We usually use Ola Hallengren's solution that takes care of Update Statistics which is inside the Index optimize job that we have. We have it scheduled for every Sunday midnight in all servers (1500+) as a standard practice.

Now, there are a few servers where there are a lot of performance troubles during the middle of the week and we run sp_updatestats for that particular database when an incident is raised for performance issues. We have a huge environment with all versions in use, 2000 to 2016. It is the 2012 & 2014 versions where we have been running the update stats manually for specific database mid-week.

Recently, we also had to schedule sp_updatestats for every day for some very active databases due to recurring troubles.

My questions :

1. How often should we have it scheduled?
2. What are the drawbacks for scheduling it too often?
3. Is there a way of evading the drawbacks with the regular updates running so frequently as I heard it takes more time to compile and might degrade performance for sometime?

Please help me with the experience you had with this.

Answer 1

How often should we have it scheduled?

You will need to decide this for yourself.

• It will depend on the modification (insert update delete)of your columns that are related to that statistics in question. You can use my script TSQL to Find Status of SQL Server Statistics to find the amount of rows modified since last updated statistics.
• Is your auto update statistics turned on?
• Size of your table because of the threshold when auto update statistics get triggered. Understanding When Statistics Will Automatically Update by Erin Stellato explains in details.
• If you find that your auto update statistics is not good enough you should consider manually updating statistics.
• If you have large tables for pre 2014 version consider Trace Flag 2371. Be mindful that it is default in SQL 2016. Erik Darling talks about it in Changes to auto update stats thresholds in SQL Server 2016.

What are the drawbacks for scheduling it too often?

Kendra Little explains in UPDATE STATISTICS: the Secret IO Explosion with some tips how to do the same more efficiently.

Is there a way of evading the drawbacks with the regular updates running in parallel?

If you meant running update statistics in parallel I suggest 2 things to be aware of.

1. Do not run statistics from same table in parallel, it will cause blocking. There has been improvement on this since SQL 2014. Read more details in Improved Support for Parallel Statistics Rebuilds by Jonathan Kehayias and a in Boosting Update Statistics performance with SQL 2014 & SQL 2016 by Parikshit Savjani of the Microsoft Tiger Team.
2. Scan phase of update statistics go parallel and has been improved in SQL 2016. Be careful with number of sessions running in parallel, you might cause other sessions waiting for available schedulers. See the related Q & A Parallel Statistics Update.

Based on the edit of your number 3 question I suggest you read Auto Update Stats Async Enabled.

Additional resources:

• When To Update Statistics?
• SQL Server Statistics Questions We Were Too Shy to Ask by Grant Fritchey
• Another Reason to Avoid sp_updatestats by Erin Stellato

Answer 2

Index reorgs (ALTER INDEX REORG) don't update stats the way index rebuilds do, and individual index rebuilds (ALTER INDEX myIndex REBUILD) only update the stats for that index.

If you're doing index reorgs (based on fragmentation percentage, etc.), you'll want to combine that with an update stats process, at least for those indexes with stats that weren't updated in the index reorg (check sys.dm_db_stats_properties):

declare @tablename sysname = 'myTable';
select t.object_id, t.name, s.stats_id, p.last_updated
from sys.tables t
join sys.stats s
  on t.object_id = s.object_id
cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) p
where t.name = @tablename;