March 24, 2010 at 9:15 am
Hi,
We want to set AUTO_UPDATE_STATISTICS_ASYNC on such that when at the time statistics generating it won't let other client connections to wait. Is there any harmful effect on setting this option or any important concern need to take care? Thanks a lot.
Thanks and regards,
Wallace Chan
March 25, 2010 at 2:09 am
If you set the option for AUTO_UPDATE_STATISTICS_ASYNC ON then the query will not wait for the update of the statistics but will execute with the current statistics and at the same time a background process will start the automatic updation of the statistics as soon as possible. This will not prevent any query request.
Once this background operation is complete new query request will use the new updated statistics. One very important thing to note here is that this option comes into play only for Auto Update Statistics event and not when manually updating statistics or on demand updating of statistics on request by a user.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 25, 2010 at 2:13 am
Chan Wai Yin (3/24/2010)
Is there any harmful effect on setting this option or any important concern need to take care?
From any blog :
[font="Verdana"]It is necessary to have AUTO_UPDATE_STATISTICS enabled first before enabling AUTO_UPDATE_STATISTICS_ASYNC because AUTO_UPDATE_STATISTICS_ASYNC is dependent on AUTO_UPDATE_STATISTICS.
In addition, Adam Machanic of the MSSQLTips.com community indicated that the AUTO_UPDATE_STATISTICS_ASYNC may be a favorable configuration for OLTP environments and may not be for data warehouse environments. The specific scenario is related to databases that have a lot of data modification throughout the day.Somewhere someone reports that he has observed the AUTO_UPDATE_STATISTICS_ASYNC configuration cause major issues in two data warehouse systems that involved near-real time loads. In both cases, a similar pattern occurred: queries against a recent time frame hit just after the load, did not trigger a synchronous update and the query optimizer chose a seek where a scan would have been more appropriate. This is due to the increased data density in that range. The bottom was really bad performance in both cases until AUTO_UPDATE_STATISTICS_ASYNC was disabled.
As with all SQL Server configurations and application scenarios, it is necessary to test the changes and validate the advantages and disadvantages based on your application and environment.[/font]
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply