turn off auto update stats

  • Hi Everyone,

    I am noticing a high number of query recompilations in my database. I have a update statistics job running daily in the AM. So I am wondering if turning off the auto update stats would be helpful to prevent recompilations. Has anyone faced such situations.

    Thanks for your inputs

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • It might, if the reason for the recompilation is statistics update. The trade-off is that your queries are using plans optimised for a data distribution that's no longer correct, so you could be degrading performance. Also, with auto stats update you are completely responsible for keeping the stats updated.

    Also, you have checked and ensured that the query recompilations are causing a problem?

    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
  • Thanks for the response Gail.

    At this point of time the recompilation is not the issue but since it is adding overhead on memory I was looking at them. Also I have verified that none of the queries were using the with recompile option to take advantage. Since I have the update statistics job running daily I thought if disabling the auto update stats wud reduce the recompilations.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Again, it might, but that assumes that the queries are recompiling due to statistics updates, and not one of the other 8 or so possible reasons.

    http://sqlinthewild.co.za/index.php/2010/11/18/recompiles/

    http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

    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
  • Thanks for the urls. I will run the profiler with events mentioned in your blog and check.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 5 posts - 1 through 4 (of 4 total)

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