locked
Slow connection to remote SQL Server 2005 RRS feed

  • Question

  • I faced a slow response issue when deploying a Dot Net C# Application to access a remote MSSQL2005 Server (Win Server 2003 platform) via a long distanced dedicated network. I am using XP as client.

     

    When deploying the application in local LAN and access the same SQL server, the application get instance response from SQL server on executing Stored Procedures as well as fetching data record set.

     

    When deploying the application to an over-sea branch using a MPLS network via dual link (2Mbps for each link) with load-sharing dedicated network (I am not sure if it is actually an VPN), the application get very slow response when calling the same SP with same input parameters to the same SQL Server.

     

    At first, I suspected that the slow response was due to bandwidth issue, but later on I found that using the same link to transmit a 2MB file just need less then 30s (around 1.5Mbps). It’s unreasonable for the application to take around 1 minute retrieving only a few hundred KB data (average bandwidth drop to 0.13Mbps).

     

    I further analyse the network packet captured by Wireshark, and found that MSSQL session was using quite a long time on transmitting RPC packets when sending table data over WAN link. Below are MSSQL packets filtered by tcp port 1433:
     
    10.1.36.147(client)-->10.9.16.46(server)
    [TCP4443]-->[TCP445](ms-ds)
    [TCP4444]-->[TCP139](netbios-ssn)
    [TCP4445]-->[TCP139](netbios-ssm)
    [TCP4446]-->[TCP1433](ms-sql-s)
    [TCP4448]-->[TCP389](ldap)
     
     
    The ms-sql session was initiated 10:14:11 and closed 10:15:00, it took 49 seconds!! Only the MSSQL session took so long time.
    Until 10:14:22 was still OK, server tried to send big segment (1460byte) properly, however, after that, they suddenly start TDS (Tabular Data Stream) until 10:14:58 as follows.
     
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    client ---> server 116 byte (remote procedure call packet)
    client <--- server 629 byte (response)
    .......
     
    During this period (36sec), they might be exchanging table information ONE-BY-ONE. This is might be the reason which causes slowness via WAN connection

     

    The source code that generated the above MSSQL session is as follows:

                // Statement for calling Stored Procedure

         public DataSet execStoredProc(string strProcName, Collection<SqlParameter> Parameters, string strDataSetName, SqlConnection sConn)

            {

                DataSet dSet = new DataSet();

                try

                {

                    sConn.Open();

                    SqlCommand cmd = new SqlCommand(strProcName.Trim(), sConn);

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.CommandTimeout = 300;

                    for (int x = 0; x < Parameters.Count; x++)

                    {

                        cmd.Parameters.Add(Parameters[x]);

                    }

     

                    SqlDataAdapter sAdapter = new SqlDataAdapter(cmd);

                    sAdapter.Fill(dSet, strDataSetName);

                    sConn.Close();

     

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex.Message);

                    sConn.Close();

                }

     

                return (dSet);

     

            }

     

     

         // Statement for accessing the result dataset

         DataSet ds = da.execStoredProc("sp_app_GetBranchByID", paralist, "ds", da.getPooledConnection(new SqlConnection()));

            AppBranch branch = new AppBranch();

     

         // The application just need a few seconds to on executing the above SP, but then need extra 36s to retrieve result set from SQL Server over the WAN link

     

         branch.ID = util.GetString(ds.Tables[0].Rows[0]["PKEY"]);

         branch.Name = util.GetString(ds.Tables[0].Rows[0]["BRANCH_NAME"]);

         branch.Description = util.GetString(ds.Tables[0].Rows[0]["BRANCH_DESC"]);

         branch.ReferenceID = util.GetString(ds.Tables[0].Rows[0]["BRANCH_FUNC_REFERENCE_ID"]);

     

         // all fetching took 36s where the total packet size of such RPC just around 600KB.

     

     

                The same RPC packets in LAN connection just take less then 1s, so the application ran much faster in LAN then in WAN. My question will be: Why the MSSQL RCP packets need so long time to transmit in WAN given that there are still available bandwidth in WAN, while other application (FTP, Windows Explorer) can fully utilize the WAN but SQL RPC cannot? Is there any fine tuning I can do?

    Thank you for your attention!
    Thursday, July 9, 2009 7:17 AM

Answers

  • If you can't upgrade then another alternative that I heard might help is SQL*Nitro, which does network compression.  I can't make any recommendations for a 3rd party product but I have heard customers indicate this product helped.

    The main issue here is window autotuning.   When SQL Server sends the response to the client, it has to wait for ACK from client before sending more data.  The window size is very small by default like 64K, so this means the server can only send up to 64K before it has to wait for an ACK from client.   If your latency is high you can see how this will delay sending data since there will be a pause and then burst of data then pause then burst of data, etc...

    On older versions of Windows you can manually adjust TCPWindowSize as I mentioned before, this increases the size of the burst of data.   You can crank this up to 10MB for example which should improve things significantly.  You have to increase this on client and server machines for it to take effect. 

    Note to get the autotuning you need to BOTH upgrade to SQL 2008 and upgrade your OS to Windows 7/Windows 2008 or later.   You can read the whitepaper to see the results, the data is quite detailed => http://technet.microsoft.com/en-us/library/dd263442.aspx


    Matt
    Thursday, September 29, 2011 5:10 PM

All replies

  • This is a well known problem with the older network stacks plus SQL Server that we fixed in the enhanced TCP/IP stack in Windows 2008 and later.  SQL Server 2008 takes advantage of these features, you can read about them here:

    http://technet.microsoft.com/en-us/library/dd263442.aspx

    Read section 1.1. Receive Window Autotuning, this is part of what fixed the problem.

    In your case if you cannot upgrade, you can manually increase the send/recv window by tweaking the TCP-IP settings on client and server you could potentially increase the throughput.  The setting is TCPWindowSize you can try manually increasing this.

    Another item that may increase throughput is increasing the network packet size setting with the SQL Server driver.  Maximum is 32K, increasing this will increase the block size that SQL Server sends to client.
    Tuesday, August 4, 2009 10:51 PM
  • Did you ever get a resolution to this?   Did you upgrade to 2008 or did you put in a work around?   We are seeing a similar issue.
    Thursday, February 3, 2011 5:28 PM
  • I would also like to know if the upgrade is a solid solution to the problem. We access SQL2005 over a WAN and the performance is shocking. Can anyone confirm this please before we put thoughts into upgrades? Thanks.
    Thursday, September 29, 2011 1:38 PM
  • If you can't upgrade then another alternative that I heard might help is SQL*Nitro, which does network compression.  I can't make any recommendations for a 3rd party product but I have heard customers indicate this product helped.

    The main issue here is window autotuning.   When SQL Server sends the response to the client, it has to wait for ACK from client before sending more data.  The window size is very small by default like 64K, so this means the server can only send up to 64K before it has to wait for an ACK from client.   If your latency is high you can see how this will delay sending data since there will be a pause and then burst of data then pause then burst of data, etc...

    On older versions of Windows you can manually adjust TCPWindowSize as I mentioned before, this increases the size of the burst of data.   You can crank this up to 10MB for example which should improve things significantly.  You have to increase this on client and server machines for it to take effect. 

    Note to get the autotuning you need to BOTH upgrade to SQL 2008 and upgrade your OS to Windows 7/Windows 2008 or later.   You can read the whitepaper to see the results, the data is quite detailed => http://technet.microsoft.com/en-us/library/dd263442.aspx


    Matt
    Thursday, September 29, 2011 5:10 PM