If I submit a query that necessitates auto creation or updating of statistics during the compilation stage and the command timeout is reached before this is finished what happens?Do stats still get updated? If so does this happen via a background process or does the timeout request have to wait until that is complete?
asked Jun 01 '11 at 04:55 AM in Default
I have now tested this. With the following test table
And the following test code
Profiler shows the following
First it creates the stats for column
It can be seen that the stats creation occurs on the same spid as the query and so this also aborts the creation of stats on column
Auto Update Synchronous
The above refers to stats creation, to test auto update of the stats I ran the above query without a timeout so both sets of stats were successfully created then truncated and repopulated the table so that the stats would be out of date and re-ran the test. The trace for that is pretty similar showing only one update managed to complete.
Auto Update Asynch
Finally just for completeness with
![Profiler 3]: h ttp://i.stack.imgur.com/S7AgK.png
If updating statistics causes the query to exceed its command timeout, the query will fail.
I do not know whether stats still get updated. Since the process is synchronous, it's possible that the stats update fails as well. To see what happens for sure, you can fire up profiler and monitor the Performance > Auto Stats event then run a query that causes a statistics update which, in turn, causes the query to time out.
To avoid this timeout situation, since SQL Server 2005, you have been able set the auto update to asynchronous with AUTO_UPDATE_STATISTICS_ASYNC ON|OFF (OFF by default) - http://msdn.microsoft.com/en-us/library/bb522682.aspx. This database-wide setting causes your query to trigger an Auto Stats event when appropriate, but then it compiles and completes using the old statistics so it doesn't have to wait for new ones. The trade-off being you run with a potentially non-optimal plan. Always a risk, especially as the statistics approach, but don't quite reach, the auto update threshold - [http://technet.microsoft.com/en-us/library/cc966419.aspx].: h ttp://technet.microsoft.com/en-us/library/cc966419.aspx
answered Jun 02 '11 at 11:48 AM
Stats are updated prior to plan compilation and it is generally a very quick process.
The stats will be updated irrespective of the timeout. While the update may be initiated due to a batch it occurs outside of it and is performed by a background process.
Setting auto stat updates to async defers the updating so the current batch isn't slowed down by the update - negligible in many instances IMHO. Without this setting the batch enters a wait condition until the auto stats update completes.If the updates were dependent on the batch and the scenario you describe were to happen frequently then the whole stats architecture would be suspect. They really need to be all or nothing operations.
answered Jun 03 '11 at 08:11 AM