Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fairly high-throughput application that occasionally decides to collapse on me. It's not very often - about once every ~3 weeks or so. When it does, if I check out perfmon, I see 100% "Avg. Disk Queue Length" pegging the server.

During these times, I also see lots of nice connection failed messages from SQL Server.

I'm no SQL Server expert, I can do the basics for indexing, taking backups, etc., but that's it.

What would cause something like this? I was thinking perhaps it was a resize of the database (it was down to ~300MB available [and it's a 30 gig database]), or maybe some reindexing gone nuts?

I do have one table in particular that has tons of inserts. Very few reads, but many inserts per second isn't unusual at all.

The server has only ~4 gig of RAM as well, but we do have a dedicated warehouse box that rolls up data every night where most of the heavy querying is directed.

Anyone got any thoughts on what might cause that huge queue length?

share|improve this question

migrated from stackoverflow.com May 9 '12 at 11:54

This question came from our site for professional and enthusiast programmers.

    
In Windows, the "average queue length" is roughly analogous to what Unixes would call "iowait." Check your hardware health. –  Charles May 8 '12 at 1:23
    
Monitor your table scans, as well as how often your instance needs to reach out to disk as opposed to the data cache. Could be hardware, but could also be an underlying cause. –  Thomas Stringer May 9 '12 at 12:33

4 Answers 4

Wait for a moment where the disk queue depth is high and find all currently running queries (scripts available on the web). It is likely that some expensive query is running. Examples: Data warehouse style queries, index operations, DBCC CHECKDB, shrinking, ... All of these are designed to drive the IO system with all might.

share|improve this answer
    
Yeah, I ran this: frozenmountain.com/Blog/post/…, and ended up with just the one highly optimized query I expected. Is my query not showing the right info (i.e., excluding index ops?) –  jvenema May 7 '12 at 23:40
    
Most queries should show up there. Very rarely, some background task like ghost cleanup does not show up. –  usr May 8 '12 at 11:46

High Disk Queue Length can be caused by a number of things, not just SQL Server:

  • Hardware failing, or misbehaving (as Charles mentions in the comments). eg: RAID controller or disks, network cards too (potentially).
  • Buggy Drivers (almost any part of the system - disk controllers, memory controllers, network drivers)
  • Other applications running. eg: Disk scans, Backups, Defragmentation, Windows Indexing, Virus Scans.
  • Other users eg: copying files to/from the drive.

If SQL Server needs to expand it's files (data, log), that could be a potential cause too if you have a slow or fragmented disk.

Windows Server 2008 and up has Resource Monitor, which allows you to see what operations are occurring on the disk. Total Bytes/sec is one indicator, but it could be lots of small operations also occurring.

share|improve this answer
    
Hmmm...do you happen to know at what point SQL auto-expands its files? I was down to 300MB available according the sp_spaceused sproc... –  jvenema May 8 '12 at 13:37
    
Oh, and unfortunately, I'm stuck on 2k3 :( –  jvenema May 8 '12 at 13:37

Anyone got any thoughts on what might cause that huge queue length?

The disc subsystem just being too pathetic to handle the load. Check whether anything special goes on during those times. If not - maybe jsut get a cheap SSD ;)

share|improve this answer
up vote 2 down vote accepted

OK, so, from what I can tell, it was related to a bunch of things:

  1. resizing files (data and log both)
  2. large number of inserts happening while 1. was going on
  3. one particularly heavy query running while 1. was going on
  4. a hard drive with some pretty high i/o times in general
  5. lack of memory on the server, so hitting the page file on top of everything else

Here were my resolutions:

  1. change the autogrowth settings so I'd have consistent growth when it happens, manually grew both the temp and primary database files to have more space, added additional vlogs to the temp database, and set up a notification so I can manually grow when the database gets below a certain space level.
  2. [nothing to be done about it]
  3. made the heavy query run less often; it was loading up data the user didn't always need, so changed it to run on-demand
  4. [working on getting a new server, this app is growing fast]
  5. [working on getting a new server, this app is growing fast]

So, anyway, it was a combination of a whole bunch of different things, mostly related to SQL, but not exclusively (so Will was correct there).

I'd love to split the answer between everyone, as they had portions of it right, but what can you do...

share|improve this answer

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.