6/5/2012 8:18:03 AM
Amit Bansal -
select is_auto_create_stats_on, is_auto_update_stats_on from sys.databases
where name = 'AdventureWorks2008r2'
select is_auto_update_stats_async_on from sys.databases
where name = 'AdventureWorks2008r2'
ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS_ASYNC ON
Doesn’t this option help in reducing downtime? :-o (if yes, then this option should come under High Availability ;)
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Visit my FaceBook page at http://www.facebook.com/AmitRSBansalContribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions.
FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346
LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
How does AUTO_UPDATE_STATISTICS_ASYNC reduce downtime?
Some DBA's does mistake by only enabling AUTO_UPDATE_STATISTICS_ASYNC to ON.
What I Mean is if AUTO_UPDATE_STATISTICS option is off and AUTO_UPDATE_STATISTICS_ASYNC is ON,
there is no effect of AUTO_UPDATE_STATISTICS_ASYNC.
It will work only if AUTO_UPDATE_STATISTICS is also set to ON.
SQL SERVER 2012 also doesn't show any error or warning if you set AUTO_UPDATE_STATISTICS to OFF
and AUTO_UPDATE_STATISTICS_ASYNC to ON.
Hi Marc, Well my last statment is asking the audience whether it reduces the downtime or not? But before I give my opinion, I have a counter question. How does online re-indexing reduce downtime?
Auto Update Statistics asynchronous will minimize the blocking. If
SQL Server determines that a statistic is out of date during, it will update
it. If it is executing in synchronous mode, it puts a hold on execution plan compile.
So you will see [Plan Guide] blocking resource description in
sys.dm_os_waiting_tasks. If you are not seeing any blocking because of that,
turning on auto update statistics asynchronous is not beneficial.
A reason to turn off auto update statistics might be you are
trying to minimize recompile events, you don't want SQL Server to take a sample
of the data, or you don't want to cause performance impacts in middle of day.
Also the auto update statics fires when 20% of the data changes and must have
at least 500 rows. Therefore many large tables might not ever get statistics
update, so Job is still required.
This is a Performance setting not a HA setting.
PS The 20% threshold setting changed with
Trace Flag, T2371, which decides its update decision based on the size of the table
How i am cheking to reduce downtime ???
Leave a comment