Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
If you stop the SQL server service does the thrashing immediately stop? –  Jack B Nimble Nov 2 '09 at 17:37
    
Yes, that is why I am certain that it is the SQL process and not something else. –  user21034 Nov 2 '09 at 18:17

1 Answer 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.

share|improve this answer
    
I'll check out CHECKALLOC. Looks interesting. Thanks for the suggestions. –  user21034 Nov 2 '09 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.