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 :
- How often should we have it scheduled?
- What are the drawbacks for scheduling it too often?
- 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.