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.

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).

share|improve this question
    
To determine that auto update statistics causes problems, have you checked Wait Stats on the server instance ? –  Kin Aug 12 '13 at 13:29
    
I've edited my post (I added P.S. section) –  Victor Aug 13 '13 at 8:55
add comment

1 Answer

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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.