Update Statistics Task (Maintenance Plan) vs auto update statistics

  • Juanita

    SSCertifiable

    Points: 5300

    Hello,

    I have an existing sql 2000 server that has a maintenance plan whose only step is to UPDATE STATISTICS on the OPTIMIZER tab. I also notice that all of my databases have the option, AUTO UPDATE STATISTICS turned on. With this option turned on, is it necessary to run that maintenance plan??? I'm under the impression that the auto update is doing exactly what the maintenance plan is doing on a scheduled basis

    I am recreating this server in a sql 2005 environment so I'm trying to clean up any unnecessary jobs while making the migration.

    Now as we are moving our db's from sql2000 to sql2005 we are running dbcc updateusage and sp_updatestats to get our stats all cleaned up.

    Thank you.

    Juanita

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Juanita (7/9/2009)


    Hello,

    I have an existing sql 2000 server that has a maintenance plan whose only step is to UPDATE STATISTICS on the OPTIMIZER tab. I also notice that all of my databases have the option, AUTO UPDATE STATISTICS turned on. With this option turned on, is it necessary to run that maintenance plan??? I'm under the impression that the auto update is doing exactly what the maintenance plan is doing on a scheduled basis

    It might be.

    Auto update stats means that when a query reads a set of column statistics and finds that they are out of date, it requests an update before proceeding with the query. For a set of stats to be out of date 20% of the table must have changed since the stats were last updated. That's a lot of rows on large tables.

    The maint plan (as far as I know) executes UPDATE STATISTICS so it updates all stats, regardless of whether they are out dated or not.

    It is possible for the auto update threshold to be too high and queries start degrading in performance due to poor stats long before the point that the auto update would kick in.

    Now as we are moving our db's from sql2000 to sql2005 we are running dbcc updateusage and sp_updatestats to get our stats all cleaned up.

    Don't use sp_updatestats there. Use UPDATE STATISTICS. sp_updatestats only updates stats that are considered outdated. When moving from 2000 to 2005, it's important to update all statistics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Juanita

    SSCertifiable

    Points: 5300

    Thank you so much !!! Great points I didn't consider and being new to sql2005, thanks for that tip!!

    Juanita

  • hi_abhay78

    SSCertifiable

    Points: 6205

    Adding to What Gail has said :

    In Auto update statistics the statistics change occure when the optimizer finds that he query is using the stale Stats , thus creating the stats at the query compiling time .Further the optimizer ensures that the smallest sampling rate is taken into consideration (ful scan for tables less than 8 MB in size)...A small sampling will not be able to give good hints to the optimizer .Its just like asking an advice on a common topin to 100 guys as compared to a nation wide voting on the same ...

    Secondly , i would also advice you to create stats on the columns that are not a part on the indexes ..this will also boost the performance ..

    Hope that adds to your decision making .

    Regards

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • hi_abhay78

    SSCertifiable

    Points: 6205

    forgive grammar and typos ...i wrote it in haste ..

    Regards.

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Juanita

    SSCertifiable

    Points: 5300

    thank you !

Viewing 6 posts - 1 through 6 (of 6 total)

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