Sql-server – Auto Update Statistics Duration and Interruptions

performancesql serversql-server-2008-r2statistics

Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts several minutes.

I’ve read that sometimes auto update statistics can slow down the server:

Is there a reason to update statistics manually?

I see that sometimes auto update statistics occurs for rather large indexes (1.7 GB size, table of 5.5M rows). I can see the date of the most recent update for statistics via STATS_DATE, but I can’t see the duration of this process.

Could you, please, suggest how can I see the duration of the statistics auto update? Or how can I determine that auto update statistics causes problems?

Thank you.

P.S.
After we’d encountered these performance problems, I began to monitor wait stats. The highest wait type is CXPACKET. It varies from 36% to 60% of all waits. And in the morning, when working day begins, it has the maximum value during the working day (users usually work with application 12 hours a day). The second highest wait type is SOS_SCHEDULER_YIELD (15%-20%).
I found some posts in the Internet where it’s mentioned that CXPACKET wait time reveals that auto update stats makes problems. But I haven’t found the real proof of such statements. It is clear for me that CXPACKET can show problems with parallel execution of SQL queries (from http://www.dbadiaries.com/reducing-sql-server-cxpacket-wait-type).

Best Answer

You can grab this data with the Auto Stats Event Class with SQL Trace. One of the event's data columns is Duration. As per the referenced BOL documentation above, here is a quote:

Duration: Amount of time (in microseconds) taken by the event.

That should give you a pretty good idea when/how long auto stats is taking. If you are seeing a lot of performance-halting auto update stats (synchronously), you could consider utilizing auto update statistics async. Turning this on will tell the query optimizer to not wait for statistics to be updated in order to continue compiling the query. This is one solution in order to prevent the direct interference of stats updates, but of course you're sacrificing fresh statistics for that particular query compilation.

For more information on this, please see the MSDN reference on Statistics.

Related Solutions

Sql-server – If a query triggers a statistics update and times out are the statistics still updated

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

Sql-server – Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts

Some info, if not a definitive answer

It's been blogged recently

When are Statistics Updated – What triggers Statistics to Update
Comment from Paul Randal

There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:

One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?

HTH...

Best Answer

Related Solutions

Sql-server – If a query triggers a statistics update and times out are the statistics still updated

Sql-server – Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts

Related Question