1

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.

Some things:

  • 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?

Thanks, Dan

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

2
  • If you stop the SQL server service does the thrashing immediately stop? Nov 2, 2009 at 17:37
  • Yes, that is why I am certain that it is the SQL process and not something else.
    – user21034
    Nov 2, 2009 at 18:17

1 Answer 1

1

Well I would try:

  1. Defragment your harddrive(s)
  2. Run DBCC CHECKALLOC and other tools to check the database and reindex tables
  3. Although I think this is very unlikely check your logical disk configuration

If these logical volumes experience high number of concurrent I/O requests, disk thrashing occurs because the hyper volumes are created from the same set of underlying physical disks. To avoid disk thrashing in such cases, care should be taken to create partitions that are likely to be accessed concurrently on isolated logical volumes and physical disks.

1
  • I'll check out CHECKALLOC. Looks interesting. Thanks for the suggestions.
    – user21034
    Nov 2, 2009 at 19:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .