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