Post reply

How often to update Statistics

  • August 19, 2011 at 11:26 am

    #242493

    Hi Experts,

    I am having a scenario that, my database is about 1TB and it is very high production server. 9AM to 9PM(12 hours) are the business hours, at this situation how often i need to update the statistics i.e what is the time frequency to update the statistics.

    Did update statistics will kill the performance of the data i.e running in the production. If it kills what is the solution for this situation.

    Your support is very needy to me.

    Thanking you in advance.

    Jags

  • August 19, 2011 at 11:35 am

    #1370918

    in my case, I have a couple of tables i need to update statistics on a couple of times a day in order to assure good performance.

    Typically you really only need to update specific tables, and not every table; Have you identified the tables that are affecting performance when the statsitics start varying a little bit?

    if you run UPDATE STATISTICS YOURTABLE WITH FULLSCAN; on that table, how long does it take?

    a 30 second slowdown when you run the command for a single table, as an example, will save lots of other queries from perforamnce degradation.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 20, 2011 at 1:29 pm

    #1371086

    Most systems I've managed we updated the statistics daily. But some were less volatile so we updated statistics weekly. You're approaching dealing with large databases at 1TB. You might need to modify your process so you do half the tables one night, half the next, or divide by thirds or something. There's no hard and fast numbers. We had a system that was so ill-designed that the stats go out of date in about 1/2 hour without firing the auto-update. So for one table in the system we were updating statistics every 10 minutes. You could tell it was happening, but it was that or watch the query plans get bad info and suddenly start performing horribly.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply