Windows – SQL Server Slow but CPU Normal

performancesql servervirtualizationwindows

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"

Best Answer

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?
Related Topic