Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've inherited a Maintenance Plans that does the following:

  • Cleanup old data
  • Checks DB integrity
  • Performs Database and Transaction Log Backups
  • Reorganizes Our indexes
  • Updates Statistics
  • Delete old backups and Maintenance Plan files

Of the 23 minute Maintenance Plan, Updating the Statistics takes a staggering 13 minutes. During this 13 minute period, access to the database is blocked (or at least, replication from this DB to our others is paused).

My Question Is:

When should we be Updating the Statistics, and why?

This seems like the kind of thing we should do less frequently than every day. I'm trying to get us out of the "just because" mind-set of doing unnecessary Maintenance.

share|improve this question
How often/how any rows get inserted/updated/deleted? To me this is the deciding factor. – JNK Jun 12 '12 at 15:43
@JNK We insert ~70,000 rows per day across the entire DB. Update ~100 rows a month. – Onion-Knight Jun 12 '12 at 16:01
1 - This is more relevant if we know by TABLE how many rows, and 2 -as a percentage. 70k rows per day into a table of 1m is a lot different than into a table of 500m – JNK Jun 12 '12 at 16:03
I would recommend using some thing like… this solution only rebuilds/reorganize and updates what is needed this way you might shortenthe time your maintinance plan takes and also save a hole lot of log space.. – Peter Feb 4 '15 at 9:52
Does Maintenance Plans delete the old transaction log starts keeping new transaction log. Please suggest what i need to do delete old transaction log after i take new transaction log backup at 12AM every day. – user60884 Mar 7 '15 at 2:33
up vote 13 down vote accepted

If you don't have the maintenance window for it, updating statistics daily is probably a little overkill. Especially if you have Auto Update Statistics turned on for the database. In your original post, you said that users are seeing a performance degredation due to this maintenance plan. Is there no other time to run this maintenance plan? No other window? I see that your plan encompasses index reorganization, when are you rebuilding indexes? When that operation happens, statistics are automatically updated (provided that isn't turned off for the index).

Exactly how often you should be updating statistics depends greatly on how much data modification your indexes and data is receiving. If there is very little modification (INSERT, UPDATE, DELETE) to the data, then you could have a more infrequent schedule for the update statistics job.

One way to find out if your statistics are stale is to look at the execution plans and if you estimated rows greatly differ from your actual rows returned then that is a good indication that the interval needs to be upped. In your case, you're going the other way and a bit of trial may be in order for you. Update statistics weekly, and if you're starting to see the tell-tale signs of stale stats then go from there.

If you are using Auto Update Statistics for your database, see this reference for the threshold of when statistics are updated.

share|improve this answer
We Rebuild the indexes once a week. – Onion-Knight Jun 12 '12 at 16:03
@Onion-Knight Then you are also updating statistics once a week when you rebuild your indexes. That could be sufficient if you're doing a blanket index rebuild across the database. – Thomas Stringer Jun 12 '12 at 17:54
"One way to find out if your statistics are stale is to look at the execution plans and if you estimated rows greatly differ from your actual rows returned then that is a good indication that the interval needs to be upped." My select statement resulted in 42K rows and it says estimated rows are 1 and the Actual number of rows are 42K. how does that work ? – Vivekh Dec 22 '14 at 14:16

When To Update Statistics?

if and only if auto update statistics feature is not good enough for your requirements. i mean if auto create and auto update statistics are ON and you are getting a bad query plan because the statistics are not accurate or current then it might be a good idea to have control over statistics creation and update. but if you are fine with your sql server performance and Query execution times.

then i suggest stopping the Updates Statistics command from your Maintenance Plans

updating statistics is important and useful 1. allows the SQL Server query optimizer to produce good query plans consistently, while keeping development and administration costs low 2. Statistics are used by the query optimizer to estimate the selectivity of expressions, and thus the size of intermediate and final query results. 3. Good statistics allow the optimizer to accurately assess the cost of different query plans and then choose a high-quality plan

If you want to do update Statistics manually you should first know When Statistics are updated automatically

If the SQL Server query optimizer requires statistics for a particular column in a table that has undergone substantial update activity since the last time the statistics were created or updated, SQL Server automatically updates the statistics by sampling the column values (by using auto update statistics). The statistics auto update is triggered by query optimization or by execution of a compiled plan, and it involves only a subset of the columns referred to in the query. Statistics are updated before query compilation if AUTO_UPDATE_STATISTCS_ASYNC is OFF

here are nice articles that speak about when update statistics is triggered in SQL server

  1. from simple-talk Section 13. When is the Auto-Update to Statistics Triggered?
  2. Section: Automating Autostats determination
  3. section: Maintaining Statistics in SQL Server 2008

after knowing when statistics is triggered it will help you decide when to update statistics manually

to know more about Statistics & its effect on performance i recommend BrentOzar and Kimberly in sqlskills very good blogs & bloggers.

share|improve this answer
Hi AmmarR, the first link seems to refer to a domain that no longer holds information about SQL Statistics. Regards. – Jaime Oct 27 '14 at 14:37
thanks i updated it – AmmarR Nov 2 '14 at 10:36

protected by Paul White Apr 14 '15 at 3:48

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.