asked
What happens if a command timeout occurs whilst auto updating of stats is underway?
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?
I have now tested this. With the following test table CREATE TABLE StatsTest ( a varchar(max), b varchar(max) ) DECLARE @VCM VARCHAR(MAX) = 'A' INSERT INTO StatsTest SELECT TOP 20000 REPLICATE(@VCM,10000), REPLICATE(@VCM,10000) FROM master..spt_values v1, master..spt_values v2 And the following test code SqlConnection connection = new SqlConnection(...); connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandTimeout = 12; command.CommandType = CommandType.Text; command.CommandText = @"SELECT COUNT(*) FROM StatsTest WHERE a LIKE '%foo%' OR b LIKE '%foo%' option (recompile)"; command.ExecuteScalar(); **Auto Creation** Profiler shows the following ![Profiler screenshot][1] First it creates the stats for column `b` successfully (the initial `SP:StmtStarting` /`SP:StmtCompleted` pair) . Then it starts creating the stats for column `a` (The selected `SP:StmtStarting` entry in the screen shot). This entry is followed by an `AUTOSTATS` event confirming that the statistics on `b` were created then the timeout kicks in. 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 `a`. At the end of the process only one set of stats exists on the table. **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. ![Profiler 2][2] **Auto Update Asynch** Finally just for completeness with `SET AUTO_UPDATE_STATISTICS_ASYNC ON` the trace looks as follows. It can be seen that system spids are used to perform the operation and they are unaffected by the query timeout as would be expected. ![Profiler 3][3] [1]:
http://i.stack.imgur.com/fusK0.png [2]:
http://i.stack.imgur.com/t5EJW.png [3]:
http://i.stack.imgur.com/S7AgK.png
Looking back at my answer I may have been a little quick and didn't adequately explain the usage of setting auto stat updates to async which does use a background thread letting the executing query complete with out of date statistics.
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][1]. 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][2]. [1]:
http://msdn.microsoft.com/en-us/library/bb522682.aspx [2]:
http://technet.microsoft.com/en-us/library/cc966419.aspx
Thanks. I was hoping that some one would know the answer without me having to resort to that. If nobody does then at some point I'll perform this experiment and post the results (the motivation for the question is because I seem to remember reading somewhere that this was an advantage of the asynch mode but I haven't read it anywhere else and can't see anything in BOL that bears that out)
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.
