SQL Server - AUTO UPDATE STATS ASYNCHRONOUSLY database option

Who is online?  304 guests and 0 members
home  »  blogs  »  Amit Bansal  »  SQL Server - AUTO UPDATE STATS ASYNCHRONOUSLY database option

SQL Server - AUTO UPDATE STATS ASYNCHRONOUSLY database option

change text size: A A A

posted  6/5/2012 8:18:03 AM  by Amit Bansal -  Views: [4516] 

Hi Friends,
The optimizer relies heavily on statistics to produce an efficient execution plan. There are many environments where I have seen that AUTO CREATE STATS and AUTO UPDATE STATS is turned on, and that’s the default too.
select is_auto_create_stats_on, is_auto_update_stats_on from sys.databases
where name = 'AdventureWorks2008r2'

I have also seen many environments where DBA’s turn off AUTO UPDATE STATS and they update the stats manually as part of their maintenance activity – and a justified reason for that is, they do not want the client queries to keep waiting while the stats are being updated in the background automatically by SQL Server. May be, they faced this issue which triggered them to turn off AUTO UPDATE STATS. Frequency of manually updating the stats could depend on many factors and I would not like to talk about that right now.
What I want to highlight is the database option that was introduced in SQL Server 2005, AUTO UPDATE STATS ASYNCHRONOUSLY. This option is not known to many and could be useful for DBA’s who turn off auto update stats as one of the downside of doing so is that the queries will have to rely on old/out-of-date stats that could result in poor execution plans.
Instead, DBA’s can turn on AUTO UPDATE STATS ASYNCHRONOUSLY which is off by default.
 
select is_auto_update_stats_async_on from sys.databases
where name = 'AdventureWorks2008r2'

This option when turn on, means that SQL Server will start updating the stats asynchronously using a separate background thread. Client queries will not have to wait for the process to complete and they can continue using the current stats while the update is under process in background. When the update process is complete, the optimizer can now start using the updated stats. Yes, this means that until the update process is complete, the optimizer will produce execution plans based on the current/old stats.
This option, though I have not seen a wide usage of it, can be handy rather than turning off auto update stats completely.
Sample code:
ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO

 

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/

Regards,

Amit Bansal

http://www.twitter.com/A_Bansal
http://www.twitter.com/SQLServerGeeks
http://www.amitbansal.net

Visit my FaceBook page at http://www.facebook.com/AmitRSBansal
Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us

tags : sql server performance, SQL Server Statistics internals, AUTO UPDATE STATS ASYNC, AUTO UPDATE STATS ASYNCHRONOUSLY, SQL Server database options, SQL Server optimizer, SQL Server reducing downtime
  Rate This Blog Entry:  register  or  login

Author

AmitBansal 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

Comments (5)

Marc Jellinek
6/5/2012 11:00:16 AM Marc Jellinek said:

How does AUTO_UPDATE_STATISTICS_ASYNC reduce downtime?

by
Sandip
6/5/2012 11:38:57 AM Sandip said:

Liked it.

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.

Thanks,

SQLCommitted

by
AmitBansal
6/6/2012 12:32:29 PM Amit Bansal said:

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?

by
Mohit Gupta
6/6/2012 4:01:37 PM Mohit Gupta said:

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 instead.

by
Debobrata sarkar
7/5/2012 1:07:33 PM Debobrata sarkar said:

Hi

How   i am cheking to reduce downtime ???

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Amit Bansal's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 152  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 230  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 258  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 156  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 1037  - Comments: 3
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 425  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 339  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 498  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1654  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1696  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2419  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...