June 1, 2011 at 11:19 am
I have a huge database with the below three options ON. I am not sure if i should be having all these three set to ON.
i) AUTO UPDATE STATS - ON
ii)AUTO CREATE STATS - ON
iii) Auto Update Statistics Async - ON
Apart from these three we also have a daily job which runs "Update Statistics on dbo.mytable"?
Can someone please advice which combination should i go or the existing one is OK?
June 1, 2011 at 11:24 am
Leave it as is.
If this hurts your server's performance during the stats update then you have a lot more to worry about than auto-stats updates.
June 1, 2011 at 11:25 am
Ninja's_RGR'us (6/1/2011)
Leave it as is.If this hurts your server's performance during the stats update then you have a lot more to worry about than auto-stats updates.
Thanks i am more concerned about having these two ON -Auto Update Stats and Auto Stats Async? My understanding was these two go in opposite way, means one does before and one does after?
June 1, 2011 at 11:31 am
Re-read bol... async means that the query won't wait for the stats to be updated before running (a new plan will be generated next time... or whenever the update is complete). It can be usefull if the update takes "long" to run.
June 2, 2011 at 7:49 am
exactly ..thats what i wanted to know is it helpfull to have AUTO Update STats( as far as i know this will update stats on tables if more than 20% of data is changed) On and Auto stats sync on?..so i think i am updating the stats before and after...do i really need to?
June 2, 2011 at 7:55 am
I C.
Auto upstats on means that stats are automatically updated. If you check async it just means that the server won't wait for the stats to build the plan and run the query. It doesn't run twice.
Now the real question is do you need to update stats more often than 20% of changes. Now that answer is a resounding yes.
Because I have all the time in the world I do daily rebuild index (only required index) & update stats on all tables with fullscan. Yes this cause a small overlap but since I usually get less than 50 000 pages in the rebuild index phase, I don't really care about re-stating those.
June 3, 2011 at 4:50 am
sqldba_icon (6/1/2011)
I have a huge database with the below three options ON. I am not sure if i should be having all these three set to ON.i) AUTO UPDATE STATS - ON
ii)AUTO CREATE STATS - ON
iii) Auto Update Statistics Async - ON
Apart from these three we also have a daily job which runs "Update Statistics on dbo.mytable"?
Can someone please advice which combination should i go or the existing one is OK?
For very large tables it is often best to leave auto update stats OFF to avoid this from firing when you might not want it to (i.e. when you need maximum server performance available). Since you are updating stats each night you should be fine here.
auto create - well, that is a bit tougher. If the table has been around for a long time and all possible code that hits it has run often then all necessary stats 'should' be created and auto create 'shouldn't' be necessary. However, I would probably leave it on since stats are just so important to the optimizer.
async update - that just allows the update stats engine to refresh stats while the existing stats stay in place, then swap out once complete. This is a concurrency thing that can be very helpful in large databases. I would leave it on.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2011 at 10:32 am
Thanks. After reading the replies sounds like i am not duplicating anything here, just will need to revisit Update stats strategy for big table. Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply