April 21, 2011 at 3:38 am
Hi
I recently noticed that Auto Update Statistics was turned off on our sql server so I turned it on as I understood that this was a good thing to have on. However since doing so the performance has degraded so yesterday I switched it off again and the performance is now much better again.
I thought that haing auto update switched on would increase performance but that does not seem to be the case here.
So the question is, should auto update statistics be on or off and should it normally increase performance?
Thanks
Matt
April 21, 2011 at 4:58 am
If you have auto update of statistics turned off, then your statistics on your indexes and tables are only good when they get created (or when your rebuild an index). Otherwise, they sit there and age. Data changes but the statistics never change. This means that the optimizer is looking at worse and worse statistics all the time and making bad choices. It looks at a value in a parameter and looks at the stats and sees that it's going to retrieve one row, when, in fact, it's going to retrieve 10,000. It picks a Loop Join operation when it should have picked a Merge and your performance is abysmal.
If you turn off auto update of stats, then you have to update stats manually, and regularly. Do you have statistics maintenance running on the system? If not, what you saw was all of your stats getting updated at once, and yeah, I'll bet it hurt performance a lot.
Unless you really know for certain that you've got a system that is supporting so much data and so many transactions that automatically updating the stats kills performance, I'd turn it on. Instead of turning it on in the middle of the day though, turn it on during your slowest time period. You'll still see a performance hit as the stats get updated, but it should be considerably reduced and by the time the next day rolls around, you should be back to normal.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 5:09 am
Thank Grant although I should have explained in my first post that the period between switching the auto update on and off was about 3 weeks so I would have thought that after that amount of time things should have been running okay
April 21, 2011 at 5:22 am
Yeah, that does make a slight difference. OK, let's look at it a different way, have you looked at the wait stats on the server? Are you waiting behind statistics getting updated? How big is this database and how many transactions are we talking about? It's exceedingly rare for auto update stats to be a problem. Usually getting your stats up to date is the solution. Are you updating stats manually?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 6:59 am
Grant Fritchey (4/21/2011)
Yeah, that does make a slight difference. OK, let's look at it a different way, have you looked at the wait stats on the server? Are you waiting behind statistics getting updated? How big is this database and how many transactions are we talking about? It's exceedingly rare for auto update stats to be a problem. Usually getting your stats up to date is the solution. Are you updating stats manually?
No I haven't looked at the wait stats, to be honest I don't really know what that is. The database is around 70Gb and we're talking about millions of transactions a day. I'm not doing manual updates of the stats but I could set up a maintenance plan to do so
April 21, 2011 at 7:19 am
Matt-1034261 (4/21/2011)
Grant Fritchey (4/21/2011)
Yeah, that does make a slight difference. OK, let's look at it a different way, have you looked at the wait stats on the server? Are you waiting behind statistics getting updated? How big is this database and how many transactions are we talking about? It's exceedingly rare for auto update stats to be a problem. Usually getting your stats up to date is the solution. Are you updating stats manually?No I haven't looked at the wait stats, to be honest I don't really know what that is. The database is around 70Gb and we're talking about millions of transactions a day. I'm not doing manual updates of the stats but I could set up a maintenance plan to do so
In general, to see what is slowing things down on the system, you'll want to look at sys.dm_os_wait_stats. That'll give you a general notion of why things are running slow. To see specifics, when a query is running slow, combine sys.dm_os_waiting_tasks and sys.dm_exec_requests (through the session_id) and you can see what they're waiting on.
At this point, I think you're right to leave it off, but I promise, reads on your system are suffering because of the out of date stats. Writes on your system, if they have to look up foreign key constraints, etc., are also suffering from the lack of stats. However, until you know exactly how things are behaving and why, I'd leave it off. Then, when you turn it back on, monitor to see what the problem is. Maybe the updated stats have caused query plans to recompile to less efficient plans, possibly because the queries are off, who knows. But I'd start with getting a very thorough baseline on performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 7:22 am
Hey Grant, when turning autostats on, would you also redo a full update stats of the system?
April 21, 2011 at 7:35 am
If I were doing it, I'd probably do that first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2011 at 7:51 am
It may be a good idea to look at using AUTO_UPDATE_STATISTICS_ASYNC as well so queries do not have to wait for the statistics to update.
May 23, 2011 at 8:31 am
Just to follow up on this, I rebuilt the indexes and everything ran tickety boo again 🙂
May 23, 2011 at 8:42 am
Matt-1034261 (5/23/2011)
Just to follow up on this, I rebuilt the indexes and everything ran tickety boo again 🙂
Thanks for the feedback. Might help someone else someday.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply