I have an instance of MSSQL 2005 running on Windows Server 2003 64-bit that is causing high queued disk IO (reads, not writes) for reasons that I can't find.
- I'm sure it is the SQL server process.
- The box is very unresponsive as a result of the disk activity, CPU is low, RAM utilization is low.
- Looking at the logs, there are some recovery actions that complete, but don't seem to be the culprits (I could be wrong, but it looks like they are completing in a reasonable amount of time)
- using 'sp_who2' I don't see anything that could account for the massive disk IO that I'm seeing.
- There is no backup job running.
- SQL Server is not trying to autoextend a data file.
What could the server process be trying to do here? Where else can I look to find out what SQL Server is trying to do under the hood?
Update: Restarting the server process doesn't help. Whatever it is that is pending resumes on restart.
Update: Further analysis using SQL Server Profiler showed that it was one of my own queries. The indexes on a table that is polled frequently were 98% fragmented, apparently causing a lot of disk activity.
Update: Rebuilding the indexes did the trick. Unbelievable that a <100k row table could cause this kind of trouble on a pretty beefy machine.