1

Our database has been giving this error occassionally... "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlInternalConnection.OnError"

I have run perfmon with below results. How can I check what is causing such high disk I/O? The Avg. Disk Queue Length counter (blue) is very high.

Thanks!

enter image description here enter image description here

7

To the question you asked:

I wouldn't rely on disk queue alone. In fact I rarely even ever look at disk queue lengths unless I'm getting in deep with a problem. It is best to look at your disk's latency. Those are the Avg. Disk Sec/Read (or /Write and /Transfer) counters. That tells you what your disk latency is from Windows' perspective. So the time that the request was taking after sent to the disk and brought back.. Disk Queuing nowadays doesn't tell you a lot because most IO subsystems are able to handle a disk queue depth and have multiple spindles doing work in your RAID group often. Finally - In this case - your disk queue length doesn't even look that bad. From here it looks like the max it was in the time of this screenshot (for the average length) was 1.377. That's nothing on most SQL Server systems. Look at your actual latency. Also I don't look at % Disk Time.. I look at the idle time instead. That is a more reliable counter and you just have to do a little math to read it.. The more idle, the less activity.

To The General Question Behind Your Question

I'll ask this one by starting with a question - Why did you go right to your IO? There could be any number of things causing your slowdown. And to answer that exhaustively here is tough but a high level of some things to look at/consider:

  • Are you experiencing blocking? I would download SP_Whoisactive and have a look at that while you are getting these errors. Do you see blocking? Do you see the query behind the request(s) that are timing out? What is the duration?
    • Have you analyzed your SQL Server Wait Stats to see what your chief cause of waits are?
    • Do you know which query or queries are causing the timeouts? If so can you look at those and see if there is any room for tuning?

There could be many other things here. This could be on the connection or network. It could be blocking, it could be a need for index tuning and query tuning, it could be that you expect the queries to take longer than the default 30 second timeout, etc.

But I'd try and gather more data and then choose a path to go down. This is an old whitepaper but it is very useful to performance tuning by waits. While there will be new wait type Tom didn't mention in this paper, it still very much applies and will help you out.

|improve this answer|||||
  • +1. Queue length is meaningless - discs are overloaded when latency goes up, not queue length. A good queue length is hardware depending - latency is not. – TomTom Dec 3 '13 at 17:38
4

Just a side note: I have heard from a few presentations that average disk queue length can be a bit skewed if you are looking at a virtual machine. With the different cache points between that VM to the physical disk, it can give you different readings. Just something to be cautious of when checking it.

When I hear an application is receiving timeout errors, the first question I ask the developers or application admin is have you changed the default behavior of the SQLClient being used. Applications I have come across lately have messed with this setting, usually increased it or ended up making it shorter.

As Mike already mentioned there are a ton of things that can be involved in this one, not just disk issues. Do you have a baseline or previous measures of that disk activity to know if it is actually increased?

With timeout errors I usually go toward checking Profiler or Extended Events for long running queries. You can find examples of this for Extended Events here and here. The quickest check would be Profiler on the specific database the application uses and just filter for duration close to the default timeout setting being used.

You can also just go after the expensive queries in your plan cache as well. I will check that if I can often just to see what is being done.

|improve this answer|||||
  • +1 - good point about extended events/profiler. Definitely also a good place to look. – Mike Walsh Nov 26 '13 at 14:34
  • Hi, One query as an example takes almost 5mins to run from the application. When I ran from SSMS it took only 30secs. How do I find out why this is? Thanks! – K09 Dec 3 '13 at 17:18
  • My CPU look high from above screenshot (first post). Could this be a problem? – K09 Dec 3 '13 at 17:34
  • I definitely think the issue is more CPU related, but without information on wait stats it is difficult to tell. I'd echo the suggestion on sp_whoisactive - we don't have enough information to help troubleshoot. – rottengeek Dec 3 '13 at 17:47
  • @k09 Time between your application and SSMS can differ for different reasons. There are other layers involved in most applications from querying the database, taking in the data SQL Server is sending, and displaying it to the user. – user507 Dec 3 '13 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.