SQL Server Slow but CPU Normal

Solution 1:

Sounds to me like a typical I/O problem (uh oh, that's what you already posted) :)

I' don't do Windows normally but the basic problems are the same:

• does it happen randomly or at certain times
  • are you running a backup in the specified time
  • are there any batch jobs
  • are there any batch jobs you do not know about (yes double check, could be some guy/girl submitting "batch jobs" from a client in some automated manner)
  • some marketing/dev Person running a nasty query that needs to go thru the whole disk
  • it takes quite some time so those people usually kill their client before the query finishes and start of another, thereby creating even more I/O
• Are your disks healthy?
• Are there any other I/O bound services running on the machine
• Networking? (that should not be the problem according to the error message but Murphy never sleeps)
• What is the historical data on server usage?
• Is the filesystem getting full?
• Did someone (intentionally or not) reschedule the priority?

Solution 2:

Well the slowdown must be either CPU or disk bottlenecked, or some kind of timeout like waiting on an unavailable network resource. You mention CPU is fine, have you checked the PerfMon Average Disk Queue Length and Disk Bytes/sec? Are there any related errors in the event log?

FWIW I have several SQL2005 (Standard and Workgroup) servers running on Hyper-V VMs and they work just fine, so there's no inherent problem.

JR

Solution 3:

Most likely the issue is contention for resources between this VM and the others on the same physical host, or there is major activity on the host itself (much I/O due to backup processes running?).

Another possibility is that your virtual server is busy doing something disk I/O intensive (other, larger or more complex, SQL queries running at the same time?) and your simple select is trying to read from disk too. Processes blocking due to I/O contention will not show up as being CPU-busy. Though unless your simple select is simple-but-queries-many-rows I wouldn't expect it to take "several minutes" even under these circumstances so host resource (probably I/O bandwidth) contention is more likely.

Solution 4:

If there is anything that slows down SQL it is slow disks, or not aligning the partitions correctly. I run a SQL 2008 instance inside a VM with an Equallogic SAN for disk and it out-performs any of our physical SQL boxes hands down.