Wednesday, July 23, 2008

Auto Update Statistics DB Option

On my very first week at work, I had to work on a production problem that had been lingering over for a while. Users were reporting sporadic application timeouts and slowness. Perfmon Data, SQL sever logs, server event logs were all clean indicating no hardware issues. Analysis of PSSDiag Blocking log and trace files showed Auto update stats getting kicked off at the problem times.

SQL server starts Auto update stats when data modification for a table is more than 20 %. Auto update stats can degrade performance especially if the table in question is big.

Consider to turn this option off if this DB option becomes a problem for you.If you decide to turn this option off then handle update statistics through a scheduled maintenance job. We can look at rowmodctr column in sysindexes to get an idea on how many modification were made after stats were last updated. If you are running SQL 2005 or SQL 2008 try out Asyncronous Auto update stats option (AUTO_UPDATE_STATISTICS_ASYNC{ONOFF})

I didn’t have the luxury of this feature as this was SQL2000. I ended up turning auto
Update stats off and having update stats run as part of maintenance.

Here’s what you would expect to see depending on the tool you use.

SQL Profiler: You would see ‘auto stats’ event class in trace files. If you import the trace files in a table then look for trace event id 58

Blocking log: In PSSDiag blocking log ( ServerName_instanceName_Run_sp_blocker_pss80.OUT)
You would see syslockinfo records like below

Spid ObjId Type Resource Mode Status
63 1893058136 TAB [UPD-STATS] Sch-M GRANT

SQL Error log: To have sql server log auto stats event in error log, run dbcc traceon (8721, -1)

You will see entries like below in the error log –

2008-05-19 23:40:51.66 spid72 AUTOSTATS: SUMMARY Tbl: DeviceEvents
Objid:1893058136 UpdCount: 12 Rows: 1124361 Mods: 124776 Bound: 225372 Duration: 5500ms LStatsSchema: 11