Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fulltext query which is usually very fast but may time out when it causes a statistics update since statistics updating is very slow on this database. Usually the query "recovers" to normal speed after the statistics have been updated but I have seen a case where the query always times out which I can only explain with the fact that the statistics are never updated (or so I think).

Unfortunately I cannot reproduce the problem at this point since we switched statistics updating to “Auto Update Statistics Asynchronously” to prevent the timeout from happening in the first place (slightly out of date statistics are not a problem for us).

So my question is

Is there a guarantee that statistics are updated on a query that times out if statistics updating is set to the (default) synchronous statistics updating (Auto Update Statistics Asynchronously = false)?

I found a reference that states otherwise - if that is the case I cannot explain the continuously timing out query case.

share|improve this question

migrated from stackoverflow.com Dec 7 '11 at 17:29

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

That was actually my question at AskSSC. I should have just tested it myself as I accepted an incorrect answer.

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%' ";
            command.ExecuteScalar();

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.

Edit

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 updated all columns of all rows so that the stats would be out of date and re-ran the test. The trace for that is pretty similar

Profiler 2

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

share|improve this answer
    
Thanks very much for the detailed answer Martin (and also for updating the original thread referenced). This explains the continuously timing out query and validates the switch to async updating as a solution. –  BrokenGlass Dec 7 '11 at 13:56

As far as I remember statistics can be created by queries if missing, but they are updated only if needed (certain percentage of modifications of the data in the table) when they are set to autoupdate. So there is no guarantee the statistics are updated.

share|improve this answer
    
-1 Does not address the very specific question asked. –  Martin Smith Dec 7 '11 at 9:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.