3

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"

Dave Cheney
  • 18,567
  • 8
  • 49
  • 56
Rob Bell
  • 183
  • 1
  • 3
  • 11

7 Answers7

3

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?
serverhorror
  • 6,478
  • 2
  • 25
  • 42
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

John Rennie
  • 7,776
  • 1
  • 23
  • 35
  • Which Performance Object group are the Average Disk Queue Length and Disk Bytes/sec in? – Rob Bell Jun 12 '09 at 10:47
  • Oops, good point. If it's Hyper-V run perform in the VM. Because Hyper-V uses para-virtualization there is no "host" for resource use to show in. If it's VMWare Server then run Perfmon in both. If it's ESX then I guess you can only run PerfMon in the guest. – John Rennie Jun 12 '09 at 10:53
  • I've run these two on the server - they started off quite low and then jumped up to 100 and stayed there for a bit, they've since started dropping back to original levels. Would this suggest virtualised hard disk problems? – Rob Bell Jun 12 '09 at 11:14
  • Yes, ADQL is the number of IOPS that are waiting - you'd want that to be in single digits for any reasonable period of time. – Richard Gadsden Jun 12 '09 at 11:30
  • If it's ESX you can log in via console and run "esxtop" and hitting "d" for disk stats. It's possible to expand with more values, but I can't remember the command right now. – pauska Jun 12 '09 at 11:36
  • I'd be very surprised if there were any problems with the virtualised disks otherwise the event log would be full of disk errors. I have a basic disk tester at http://rattus/ratsauce/index.html#diskthrasher. It is very basic but should expose any serious disk performance problems. I think it's more likely something on your server is caning the disks. NB when the queue shows 100 it's usually >100. You can change the scale to see what it actuall is. – John Rennie Jun 12 '09 at 11:59
2

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.

David Spillett
  • 22,754
  • 45
  • 67
  • +1. Eliminating the virtualised element from this problem would be a good place to start, if the physical box is otherwise heavily loaded – tomfanning Aug 28 '09 at 09:26
1

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.

BillMorton
  • 104
  • 1
  • 6
0

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.

pauska
  • 19,620
  • 5
  • 57
  • 75
0

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.

Joel Mansford
  • 985
  • 1
  • 5
  • 13
0

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.

Jeff Hengesbach
  • 1,762
  • 10
  • 10