How often SQL Update Statistics job recommend to run

  • Hi,

    Just a quick question. Probably would hve answered earlier in this forum, but I couldnt found an answer which relate to me.

    In a SQL server (SQL2005) which runs financial systems of an orgnization, how often it is recommend to run the Update Statistics job?

    Microsoft recommend not to run th job often as it clears query cache. Im just wondering whether it should be an overnight job or a job which runs once in a monthly/ weekly.

    Appreciate your suggestions/ recommendations in advance.

    Thanks.

  • If you mean UPDATE STATISTICS WITH FULLSCAN, weekly or monthly is probably enough for most situations.

    However, I often setup a job to run nightly to run EXECUTE SP_UPDATESTATS in active user databases, because it only updates the stats that are needed. It takes much less time and resources to run.

  • Yes, WITH FULLSCAN

    I decided to go for once a month job at this stage. Feel that's sufficient, Might keep weekly as an option.

    Thanks Michael.

  • dakshiw (6/26/2011)


    Yes, WITH FULLSCAN

    I decided to go for once a month job at this stage. Feel that's sufficient, Might keep weekly as an option.

    Thanks Michael.

    Not nearly enough.

    The 1 issue that's going to bit you in the arse is when you have a [small]datetime column that is insterted with the value of today or getdate().

    The stats will soon become stale and cause you to have bad plans >> the server will expect 0-1 rows to be returned instead of 1000s.

    Here's the full story :

    http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

    I'm working on something to solve this issue but it's not completed yet. Ping back if you don't hear from me in the next couple of days.

  • It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.

    "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

  • Grant Fritchey (6/27/2011)


    It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.

    Full scan every 15 mintues????????????????

    I sure hope it didn't take 14 minutes to complete that scan :w00t:.

    Edited typo.

  • Is it worth the effort to identify table with high index fragmentation and then update stats for those tables more frequently instead of doing a full scan

    Jayanth Kurup[/url]

  • Ninja's_RGR'us (6/27/2011)


    Grant Fritchey (6/27/2011)


    It really depends on the volatility of the data, the structure of the indexes, and the types of queries run against it. We updated statistics on an insurance system I used to manage nightly for most databases. A few were weekly. We even had one table that, due to very poor construction, had it's statistics updated with a full scan once every 15 minutes. So there's no one right answer.

    Full scan every 15 mintues????????????????

    I sure hopre it didn't take 14 minutes to complete that scan :w00t:.

    Ha! No, it took about 2 minutes though, but it was absolutely necessary. The data in the table would skew so badly and changed so frequently that if we didn't update the stats, we were almost guaranteed a bad execution plan 3-4 times a day due to bad parameter sniffing. It was messed up to say the least, but the constant update on the stats didn't hurt the system, it helped.

    "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

  • Jayanth_Kurup (6/27/2011)


    Is it worth the effort to identify table with high index fragmentation and then update stats for those tables more frequently instead of doing a full scan

    I actually do both. Reindex only what needs to be worked on. Then update all stats with full scan (I can fit this in my window so I just do it).

    Yes I know this causes an overlap on some stats because I do rebuilds on a couple indexes daily but since that's well under 0.1 of the indexes I don't care.

    Never had a single parameter sniffing issue in the 6 months+ doing that.

  • And here's what I use to reindex... works great out of the box. No tuning necessary at first. I only recommend starting with higher than 10% fragmentation because in the first few run(s) you'll have more index to work on.

    http://sqlfool.com/2010/04/index-defrag-script-v4-0/

  • Thanks I had suggested the same to our DBA folks , haven't heard back from them though 😀

    Jayanth Kurup[/url]

  • I have a similar problem, there is a database in which a large table with more than 10 Million rows and on daily basis we are adding 1,32,000 rows.

    The maintenence plan that we run is taking more than 8 hours to rebuild index and update stats.

    i am more concerned about update stats here, though the default sql server maint. plan does it with full scan it looks like this is the reason why it takes more time.

    Now i am planning to setup a job with EXECUTE SP_UPDATESTATS which will run every night and update the stats. This should take less time? and will it be usefull?

    thanks

  • Kishan Singh (7/30/2012)


    I have a similar problem, there is a database in which a large table with more than 10 Million rows and on daily basis we are adding 1,32,000 rows.

    The maintenence plan that we run is taking more than 8 hours to rebuild index and update stats.

    i am more concerned about update stats here, though the default sql server maint. plan does it with full scan it looks like this is the reason why it takes more time.

    Now i am planning to setup a job with EXECUTE SP_UPDATESTATS which will run every night and update the stats. This should take less time? and will it be usefull?

    thanks

    Even with 10 million rows, I wouldn't expect the statistics update process to take more than a few minutes unless there are blocking issues.

    Be careful about rebuilding indexes AND updating stats. If you rebuild the index, you have new statistics, based on a full scan of the index. If you run sp_updatestats on that, you're going to get a sampled scan of the index, which means less accurate statistics. In short, you may have just made things worse as far as the statistics go.

    "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

  • Thanks for reply..

    So what do you recommend me here, the server is old and has just 4GB's of RAM. we are going to upgrade but it may take few months. The time window allowed for maintenence is just 5 hours. Earlier i was running rebuild indexes followed by update stats with full scan (the default sql server maint. plans).

  • To get fine control over that type of maintenance, I'd suggest getting a copy of Michelle Ufford's scripts or Ola Hollengren's scripts. They'll do a much better job than Maintenance Plans will. This is especially important since you have such small amount of resources.

    "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 15 posts - 1 through 14 (of 14 total)

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