4

I have often found that when I am having performance problems on my MSSQL database, I can resolve them by running sp_UpdateStats. I learned this because that's always the first line of defense from tech support, right after, "Is your computer turned on?"

My understanding is that SQL Server is supposed to keep stats automatically; I shouldn't need to nanny it. So I'd like to understand: what circumstances might lead to the stats falling out of date so badly that I need to update them manually?

Shaul Behr
  • 2,963
  • 8
  • 34
  • 42

2 Answers2

3

As the data in a table changes the statistics objects that represent the distribution of data become outdated. This is a problem because it's the statistics objects that the optimiser uses to make it's estimates when building execution plans. If these estimates are inaccurate your performance will suffer.

SQL Server does automatically update statistics objects when the amount of data in the table that has been changed hits certain thresholds, but if you have data that is changing frequently or tables with millions of rows you will better off performing regular index maintenance.

This can be done with maintenance plans or scripts. Ola Hallengren's suite of scripts is very good for this and something that I use in all of my environments. There is also the Minionware suite that I have not used personally but I hear good things.

James Anderson
  • 5,794
  • 2
  • 27
  • 43
2

These two answers from Stack Overflow are quite good:

Summary:

sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

Thus, you could run UPDATE STATS every day and it might not do anything. I run my update stats when I reorganize my indexes once a week. I do this on the weekend at some early time so that no one complains if the system gets slow. So far, this has worked for me and I hardly get any issues.

Basically it only makes sense when your data is changing more frequently than the rate of auto stats.

Community
  • 1
Philip
  • 31
  • 5