locked
connection timeout RRS feed

  • Question

  • My method executes lots of asynchronous SQL requests and I constantly get connection timeout exceptions. What else can I do except increasing the connection timeout value and proper indexing? I mean with the database part, not with the code part. Can't change the code part. Besides, the application is running fine on different servers, but only I experience those timeout exceptions on my pc and local MS SQL Server 2008 R2 database (which is also on the same PC). So I think this is clearly a performance issue since the connection timeout is already set to 3 minutes. Maybe there is something I can change on the server? Maybe there is a number of simultanious requests constraint? Each of my requests needs clearly less that 3 minutes, but there are about 26 000 of them running asynchroniously, and only I experience those problems on my local PC and local DB.
    Wednesday, April 13, 2011 12:39 PM

Answers

  • Thanks for your reply, John. I figured out that I have a command timeout set, not the connection timeout. I need to figure out where to set the timeout value, I am also planning to attach a profiler and see where it gets me. I've noticed in the performance monitor that SQL Server eats up +200 MB, but I still have 1 GB of RAM free. I don't remember the details about CPU threads, but I'll try to elevate SQL Server process'es priority and see if it solves anything.
    • Marked as answer by Ai-hua Qiu Friday, April 22, 2011 7:37 AM
    Thursday, April 14, 2011 7:52 AM

All replies

  • There are 2 timeouts for all the data access APIs to SQL Server:  Connection timeout, and Command Timeout.  If you are experiencing a connection timeout, then most likely the application is failing to connect to SQL Server at all -- the common causes for this are thread starvation associated with heavy CPU load.

    Looking at your problem, it appears as though for a local->local setup, the combination of client/server load on the same physical hardware is starving SQL Server from the CPU resources needed to listen for incoming connections.  The overhead needed to do this is very low, so you must be seeing nearly 100% CPU utilization on that machine.  My proposal is to either: 1.) split the client and server for your local testing, 2.) reduce the workload when testing local/local to keep CPU utilization below ~95% to maintain a healthy system, or 3.) scale up your local/local testbed to provide enough hardware resources to meet your testing needs.

    I would propose investigation of the Async mechanism and the tightness of the polling loops for CPU efficiency, but you said you can't change the code so we are left with configuration options for workarounds.

    Since the CPU is being used by both client and server on the same system, and hardware is expensive, I think the best bet is to lower the number of simultaneous client processes running on the same machine as the server.

    Hope that helps,

    John


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Wednesday, April 13, 2011 4:23 PM
  • Thanks for your reply, John. I figured out that I have a command timeout set, not the connection timeout. I need to figure out where to set the timeout value, I am also planning to attach a profiler and see where it gets me. I've noticed in the performance monitor that SQL Server eats up +200 MB, but I still have 1 GB of RAM free. I don't remember the details about CPU threads, but I'll try to elevate SQL Server process'es priority and see if it solves anything.
    • Marked as answer by Ai-hua Qiu Friday, April 22, 2011 7:37 AM
    Thursday, April 14, 2011 7:52 AM