通过

SQL Server Query Performance when running query from a Windows 2008 Server

  • 项目

Recently I faced two cases in which we can see slow query performance when we run a SQL Server query from a Windows Server 2008 client machine. The query takes more time to complete when we run it from a Win2K8 machine as compared to a Win2K3 or WinXP machine. To test this we opened a SQLCMD.exe from the SQL Server 2008 machine and ran the following command:

 

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\dipanb>cd \Program Files\Microsoft SQL Server\100\Tools\Binn

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>sqlcmd -S GODFATHER1 -U testuser -P testuser -l 5 -Q"select 1"
HResult 0x102, Level 16, State 1
TCP Provider: Timeout error [258].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in login response.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn>

 As we can see if I reduce the connect timeout to 5 sec, we get a connection timeout. From a good client machine running on W2K3 Server or WinXp, if I set the timeout to 1 sec, I am still able to make a successful connection and I get a row returned. So what is different in the Windows 2008 Server machine? To find out what is happening, I took a Network trace using Microsoft Network Monitor 3.3

Capture

We can see a delay of approx 5 seconds in the trace. This tells us why we were getting a timeout if we reduced the connect timeout to 5 secs because the delay happened before the pre-login handshake.

The other customer facing this same issue was running a Linked Server query.

In this example, the Linked Server was on a Windows 2008 Server running from a SQL Server 2008 Sp1. It was querying a SQL Server 2005 running on Windows Server 2003. When I run the query through the Linked Server, it was taking 55 secs to return 64 rows. If I run the query directly from the 2005 SQL Server, the result is instantaneous. To troubleshoot this I took a Network trace from the Primary Server where the Linked Server is located and also on the Destination Server, from where the Linked Server is pulling data.

Capture1

We are seeing the same 5 sec delay here as well.

Resolution

This delay may happen when your NIC Card or a router is not compatible with a few features which I am describing below:

The Scalable Networking Pack (SNP) feature includes TCP Chimney Offloading, Receive Side Scaling (RSS) and Network Direct Memory Access (NetDMA). For a Windows 2008 Server, TCP Chimney is disabled but RSS and NetDMA are enabled by default. The other factor which may contribute to the delay is Receive Window Auto Tuning feature which was introduced since Windows Vista SP1.

It is recommended to follow up with your network adapter vendor to see if they have an updated driver that will address the problem and allow the use of the above features. As an immediate relief you may turn these features off.

1. Receive side scaling (netsh int tcp set global rss=disabled) This was causing 15 sec latency.

2. Autotuning level (netsh interface tcp set global autotuninglevel=disabled) This was causing 52 seconds latency.

You can run the following command to get the TCP IP global values
netsh int tcp show global

After you disable the above settings your tcp global values should look like this:

image

Note: You may not see this behavior on all Windows 2008 Server when we have these values turned on. These happens only if you have an old router or NIC card and they are not compatible with these settings turned on.

More Information

https://support.microsoft.com/kb/951037

https://support.microsoft.com/kb/947239

Comments

  • Anonymous
    March 27, 2011
    had this problem with citrix servers windows 2008 accessing or better "not accessing" their remote sql database. After disabling autotuning level everything worked fine !

  • Anonymous
    February 13, 2014
    Thank, this solved our performance issue between windows server 2008 en windows server 2003 sql 2005 communcation over linked servers.