x

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?
more ▼

asked Jun 01, 2011 at 04:55 AM in Default

Martin 1 gravatar image

Martin 1
203 8 9 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

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

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]

[3]: http://i.stack.imgur.com/S7AgK.png
more ▼

answered Dec 07, 2011 at 03:24 AM

Martin 1 gravatar image

Martin 1
203 8 9 11

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.
Dec 07, 2011 at 05:14 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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][2].

[2]: http://technet.microsoft.com/en-us/library/cc966419.aspx
more ▼

answered Jun 02, 2011 at 11:48 AM

KenJ gravatar image

KenJ
20.4k 1 4 12

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)
Jun 02, 2011 at 10:37 PM Martin 1
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jun 03, 2011 at 08:11 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

This is not correct. I have tested in profiler and that shows stats maintenance happens on the same spid as the query execution.
Dec 07, 2011 at 03:23 AM Martin 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34
x9

asked: Jun 01, 2011 at 04:55 AM

Seen: 2309 times

Last Updated: Jun 01, 2011 at 04:55 AM