How often should we use Update Statistcs.(More frequent or Not)
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.
- 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.
- 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