turn off auto update stats

Question

turn off auto update stats

Pyman

SSChampion

Points: 13830
More actions
January 9, 2012 at 3:33 pm

#262675

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Pyman SSChampion Points: 13830 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Pyman SSChampion Points: 13830 More actions · Answer 4

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