47

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.

| improve this question | |
  • 2
    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 ola.hallengren.com/… 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
29

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.

| improve this answer | |
  • We Rebuild the indexes once a week. – Onion-Knight Jun 12 '12 at 16:03
  • 5
    @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
26

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. support.microsoft Section: Automating Autostats determination
  3. msdn.microsoft 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.

| improve this answer | |

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