February 10, 2009 at 2:19 pm
I realize this is like arguing religion, Ihave a 3rd party app (OLTP mostly inline sql very few sp's) recommending I turn autostats off. I thought for OLTP and inline sql this was NOT the way to do it. If I would do this on the server they had a script to rebuild statistics every weekend. I am already rebuilding indexes for all tables twice a week. Am I correct in when you rebuild the indexes the stats also get done?
February 10, 2009 at 2:50 pm
Rebuild, yes, reorganise, no.
Even still, autostats should be on for any system unless you know that 1 week old stats will not result in performance degradation due to poor query plans.
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
February 10, 2009 at 3:01 pm
Not at all like arguing religion. For this one, you can create very precise metrics.
Run the database with auto-update turned on for a few weeks, have a trace running that records how long batches take to complete.
Then run the database for a few weeks with auto-update turned off, and the same trace running.
Compare results.
You'll have solid metrics and real numbers and will be able to tell if it's going to matter.
If the database is loaded by a weekly ETL process and has no updates, or insignificant updates, during the week, the autostats thing might be true. Otherwise, I'll put money on autostats ON being the winner.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 3:32 pm
Thanks, for an OLTP I still would argue they should always be on. An in a case where everything is in line sql it would be a must. Hard to argue with a vendor whose product is all in line non ansi compliant tsql, wonder how they every got to that
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply