the effect of clicking auto update statistics on and off quickly in succession

Question

the effect of clicking auto update statistics on and off quickly in succession

Liam Willis

Old Hand

Points: 376
More actions
September 19, 2008 at 8:03 am

#154742

I was having performance problems with a pretty heavy process that was run every morning on one of my databases. I wont bore you with stats or details but the upshot was that after googling, tweaking and tracing anything remotely connected to it, it turned out to be a SAN issue (well, so we think). What was happening was the LUN our database files were sitting on were sharing resourses with some other busy servers on the SAN. We only touched upon this by accident and to resolve it we isolated the LUN so that when the other servers had a spate of activity it no longer affected my database server.
As a result performance went back to improved and more consistant levels. This has lasted without incident for over 6 weeks until now.
This process normally runs in about 15 minutes but 3 times this week it will run to a couple of hours or more, very much like the old performance problems we were having. The funny thing about this is that when this is an issue, the process has to deal with its usual 4000 records and each record takes between 1 and 2 seconds to process but if I switch on and off the auto update statitsics option on the database it immediately jumps to processing a record in less than 50 miliseconds and the entire process runs to completion within a couple of minutes. I'm obviously delighted to have some sort of solution to a problem that flumoxed us for months here (one we though we had sorted through a hardware change) but I dont know for the life of me why it works better after doing this. Has anyone any idea what affect switching this option on and off has on the sql server engine?

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

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

you say you switched auto update statistics ON then OFF? Is that on just before the process and off again afterward?

If so suggests you have auto update stats off by default so perhaps your stats are out of date and slowing up the process. If so either switch on auto update stats or run sp_updatestats or update statistics just before the process.

Apologies if I have interpreted your question wrong.

The statistics hold info on the spread of data in your tables, e.g. as a simple example the percentage of females and males in a table. The optimiser needs this information to be accurate to make a good job of building the query plan. So if the stats are out of date query plan can be way out and this will seriously affect performance.

---------------------------------------------------------------------

Liam Willis Old Hand Points: 376 More actions · Answer 2

Hi George

This is a very busy database and we took the decision a couple of years ago to switch off the auto update statistics as it was affecting performance. We now rebuild our indexes and do our optimisations through a scheduled task every night. So your interpretation is of my poorly written mail is correct, the auto update was off already. What puzzles me is why switching it on and then immediately switching it off again, boosts performance so rapidly.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

do you do an index rebuild or a reorg. If a reorg this does not update your stats, rebuild does.

Either way, as this is a busy database it sounds like for the table(s) involved in the process, the stats get out of date quickly, so your solution is most likely to incorporate an update stats into the beginning of the process.

I have seen this sort of scenario before and the update stats can make an enormous difference.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 4

Liam Willis (9/22/2008)
Hi George
This is a very busy database and we took the decision a couple of years ago to switch off the auto update statistics as it was affecting performance. We now rebuild our indexes and do our optimisations through a scheduled task every night. So your interpretation is of my poorly written mail is correct, the auto update was off already. What puzzles me is why switching it on and then immediately switching it off again, boosts performance so rapidly.

Switching it off doesn't cancel the rebuilding of stats that started to rebuild. My recommendation would be to rebuild the stats on the tables involved in the process just before the run.

Many will disagree with me, but I think turning auto stats off is a form of "death by SQL". I've found stats to be almost as important as having the correct indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)