We're having some trouble with SQL Server hosted on a virtual server. We occasionally have short periods where even performing a simple select takes minutes. During this period the CPU and memory usage on the server appear normal. We've performed some basic monitoring of the server using performance monitor but this hasn't uncovered anything and we're out of ideas. What would be the next step in diagnosing the problem?
EDIT:
The only signs of the problem we are seeing on the server are Event Log messages informing us that "SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete"
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:
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
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.
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.
If the virtual server doesnt indicate any problems at all it's probably the host server having problems. These problems are often seen where people put way too many virtual cpu's on each virtual server, it increases the chance of blocking the physical hosts cpu.
I would imagine that the answer is already within one of the other responses. However it's also worth running EXEC sp_Who2 and ensuring that your 'simple queries' aren't being blocked by some kind of long-running update or similar.
I've been caught out before with MS Access linked tables which have some awful locking habits.
Tell us a bit more about the storage supporting your host / sql vm (local, iscsi(hw/sw), nfs, san, sas, sata, vendor, etc)? From your most recent comment that sounds like a good path to examine more closely.
Let us know what your hypervisor (and potentially host OS is) so folks can speak more directly to your situation.