| Ciaran 2006-11-16, 7:12 pm |
| Hello
We're running SQL Server 2000 SP3 on a Windows 2003 Server platform. We're
occasionally seeing queries and updates that run fine 99% of the time (i.e.
completing in tens of milliseconds) taking seconds to complete. I've
confirmed that the delays are at the SQL level (and not at the application
level) using SQL Profiler, and I've even caught one trace where the SQL
Server Agent's execution of sp_sqlagent_get_perf
_counters took nearly 6
seconds. This would indicate a general server response issue to me rather
than a specific table/index design problem.
I've tried logging the execution plan statistics as part of the profiler
traces, but the plans used when commands get stalled are exactly the same as
the plans used when they run normally.
Using the track_waitstats stored procedure for sampling process wait states,
I have seen a large spike in WRITELOG wait times around the time of stalled
commands. The classic recommendation on that is to look at disk I/O, but I've
run counter logs on the recommended values (avg. disk read/write queue
length, avg secs per read/write, reads/writes per sec, split I/Os per sec
etc) and nothing is showing up as hugely abnormal. I would have expected disk
I/O problems to show up in other areas of the system performance too but our
occasional SQL delays is the only symptom we're seeing. The max CPU load I've
logged is 30%.
Has anyone else seen anything similar?
|