11

Is it safe to run sp_updatestats on an SQL Server in the production environment?

Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs and cause timeouts or other issues for the users?

Torbjörn Hansson
  • 255
  • 1
  • 2
  • 9

1 Answers1

11

That's a big 'it depends.' Depending on how your statistics have been maintained and the options you specify you could end up running full table/index scans and thrashing your I/O and buffer pool. Depending on the characteristics of your hardware and databases that could be very bad.

Also, rebuilding statistics invalidates execution plans, which means you could see a CPU spike and slower performance while SQL Server re-compiles queries.

Best practices dictate updating statistics during off-peak hours to minimize impact. Otherwise, take due precautions to minimize load on the system such as rebuilding statistics on only the tables that require it over a period of time.

Check books online for more information:

http://technet.microsoft.com/en-us/library/ms173804(v=sql.110).aspx

http://technet.microsoft.com/en-us/library/ms187348.aspx

Jon Boulineau
  • 611
  • 5
  • 8