This is a static archive of our old Q&A Site. Please post any new questions and answers at ask.wireshark.org.

Trying to determine slow/unresponsive issues on a SQL server

0
1

Greetings,

I work for a medium sized healthcare institute of about 700 employees. We run an EMR (Electronic Medical Records) system that is responsible for just about everything we do. It consists of two servers, one 2008 R2 Application Server and one 2008 R2 MS SQL Server. For a good while now we have had several complaints of random slowness or lockups within the application which results in angry physicians and nurses and ultimately slows down the company as a whole by increasing visit times due to a slow system.

To the end use this issue looks like a drop in network connectivity but I have been able to debunk this by checking switch logs, event viewer logs, and other various traps and monitors I have setup throughout the network. The servers themselves appear to be generally OK but that is not exactly my area. But working with the systems administrators we feel that the issue is more or less with our SQL server.

I used Wireshark to take a capture of the traffic going across it but I'm really not well versed in Wireshark enough to understand fully what I am looking at. One thing does stand out is a bunch of the following...

alt text

The destination 10.30.10.10 being our SQL database server and the source 192.168.75.4 is one of our terminal servers. I've researched a little on TCP zerowindow but I don't fully understand it or how to go about resolving it.

I would appreciate any help anyone could give. Thank you,

-Adam

asked 16 Oct '15, 11:48

Adam%20Barnett's gravatar image

Adam Barnett
1121
accept rate: 0%

Could you provide us a trace?

(16 Oct '15, 11:54) Christian_R

Here is an example of what I see.

https://www.cloudshark.org/captures/18b46be08aac

(16 Oct '15, 14:23) Adam Barnett

That are really large segments above 50k and greater (TCP Offload enabled). I don´t think that is the cause of your prob, but it makes it even harder to say something reliable.

From my point of view it would be better to take a trace at the 10.30.19.113. Because there is the prob and you can hopefully (RSC disbaled) see the segemnts like they are on the wire...

(16 Oct '15, 15:13) Christian_R

One Answer:

2

Your terminal server is not offering TCP window scaling therefore the offered TCP window-size cannot exceed 64k.
So for starters you need to enable RCF1323 (should be automatically enabled with auto-tuning in windows.
TCP 1323 Options from http://www.speedguide.net

HKEY LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters Tcp1323Opts=1

(DWORD, entry created automatically by Windows when you run the "netsh int tcp set global autotuninglvl=…" command, set to 0 by default).

Setting this seems to have no effect, since auto-tuning uses the TCP 1323 scale factor and changes it on the fly, disregarding this setting. Additional testing may be required to determine it’s effect if auto-tuning is turned off. Setting it to 1 is best for broadband connections.

alt text

answered 16 Oct ‘15, 23:02

mrEEde's gravatar image

mrEEde
3.9k152270
accept rate: 20%

@mrEEde: Do you have another nice link about this auto tuning behavior?

(16 Oct ‘15, 23:54) Christian_R

This is one of the outputs from the terminal server.

[1]:  photo 2015-10-19_8-05-58_zpskernl5ml.png

(19 Oct ‘15, 06:09) Adam Barnett

So I’d say you need to enable Auto-Tuning. https://support.microsoft.com/en-us/kb/947239

(19 Oct ‘15, 09:28) mrEEde

Receive Window Auto-Tuning is set to normal on the SQL server and disabled on the terminal servers.

I will run this by the server folks first before I set it.

(19 Oct ‘15, 12:46) Adam Barnett