0

I've got a SQL 2005 database which has some maintenance plans on it set up by someone else. One runs Update Statistics on all databases early in the morning. It seems this is causing tempdb to fill up and hence breaking some other maintenance tasks which are running at the time. It's not successfully run for at least a week now. I've got Auto Update Statistics set to true though, so do I even need to be running Update Statistics every morning? There are other maintenance plans including a nightly reindex job, I don't know if having that then requires that the statistics are updated.

thanks,

Robin
  • 817
  • 3
  • 11
  • 20

2 Answers2

1

Although Update Statistics makes use of tempdb(Click here for more on Linchi's Blog) shouldn't really blow it unless it is set extremely small size.

Although you have Auto Update Stats switched on it is recommended to update the stats regularly for all queries to make use of the latest stats reflecting the most current data distribution.

In response to your question Yes you can switch off the job though you may wish to consider amalgamating the Update Stats job into your regular maintenance plan where in replace the explicit "Update Statistics" with "sp_updatestats" which will only update stats on those tables which require it.

Also, as a best practice you might wish to consider just re-organising the indexes which have less than 30% fragmentation and rebuild only ones which have more than 30% fragmentation. More info can be found under Section D towards the bottom of the page here http://msdn.microsoft.com/en-us/library/ms188917.aspx

Hope this helps.

Chirag
  • 155
  • 8
0

The Update Statistics setting can be disabled - I don't know why you have a separate maintenance task for this though - just keep in mind that you'll need to run UPDATE STATISTICS if you want to update the query plans for anything.

If you rebuild an index (also not necessary every day if the indexes are not fragmented), the statistics are automatically updated.