SQL Server Connection Problem case resolution (1) reprinted from: Http://blogs.msdn.com/b/apgcdsd/archive/2015/04/27/sql.aspx?CommentPosted=true#commentmessage
Microsoft Network Monitor (Netmon) is a Web protocol data analysis tool released by Microsoft that leverages Netmon to capture and view and analyze network data. Netmon often plays a key role when dealing with connectivity issues with SQL Server. In this blog post, I'll share a classic case that you've solved by using Netmon.
In this case, the customer discovers that an error occurs when executing a query in the client's SQL Server Management Studio, and the error message is
"Connection forcibly close by the remote server". To investigate why the connection was closed, we crawled the Netmon on the client and server side. Before we formally analyze this case, let's start by introducing some knowledge and usage skills about Netmon. Netmon interface
1. In the Frame Summary section of the Netmon interface, we can first see frame number, whether or not we have set the value of filter,frame numbers in our browsing, which is equivalent to a row of frame. 2. In the left network conversation, we will see the process name and ID, which in the example is Ssms.exe and 3352. When we continue to expand and see IPV4, we can know where this conversation came from. Expand again to see this conversation port, in this example, the port is
1433To
49428。 Here's an additional explanation of what ports will be used when the client program creates a connection to SQL Server? The client requests and uses a dynamic port to the operating system and sends a connection request to SQL Server. If you are using machine name,provider when connecting, the default is to connect to port 1433, which is a
provider's behavior, changing this behavior requires the registry to be modified: Hkey_local_machine\software\microsoft\mssqlserver\client\<provider>\tcp\defaultport
3. When the client tries to create a connection to SQL Server, with source port and destination port, a set of physical TCP connections are formed in these two ports. After a connection is formed, the sequence value of the package changes when each packet is sent.
Note that only in the same physical connection, the sequence change is continuous。 If the client and SQL Server establish two different physical connections, the sequence in the two connections have no relationship.
4. Netmon The amount of data is very large, if you look at it? For example, when looking at a larger netmon, we found a reset Flag:
3:46:20 PM 9/5/2014 21.8000442 Ssms.exe 172.22.204.237 172.16.221.38 TCP
TCP: [Bad checksum]flags= ... A.R., srcport=49428, dstport=1433, payloadlen=0,
seq=3636257929, ack=707503184, win=0 {tcp:2,ipv4:1}
You can start with the details of this frame. From the details it can be found that the source port is 49428. (1) At this point we can filter out the reset conversation by adding filtertcp.port==49428.
(2) Another method of filtering is to select Find conversation directly in the menu that pops up after right-clicking the frame, but this method may cause drops, so it is recommended to use the first method.
Finding the port is almost the equivalent of finding a connection to the problem. Of course, for different points of time, the same port could be two different connections, since the last connection was turned off,
the next connection is likely to reuse this port。 After filtering by port, look ahead from the reset start sequence.
5. In the Netmon we can also see some protocol is the TDS frame. The benefit of TDS is that when viewing frame details, you can see more information. For example, if we look at a tds:sqlbatch TDs Frame Detail, we can see the SQL statement:
If the port used by SQL Server is not the default of 1433, how to display TDS frames, you can refer to this blog post: How to enable TDS parser to display TDS frames when SQL Server is Li Stening on port other than default 1433
6. In addition to filtering via port as described in 4, the most common filter
ipv4.address==<xxx.xx.xx.xx>
In addition to writing directly in the filter, there is a way to add filter to filter out all flags are reset frames as an example to illustrate: Then I can find this flag in the details of a reset frame, In the right-click popup menu, select Add Selected
Value to Display filter:
7. A SQL Server package passes through the following layers during network transfer:
NIC (network card physical device)-"NDIS (NIC driver)-" TCPIP (operating System)-"AFD (operating system background thread, each TCP port will have an AfD)-"
SQL server-"for authentication (call SSPI-LSSAS-DC)
The
data captured by the Netmon is in the NIC driver above and TCPIP below the。 So Netmon caught by the packet is not a real packet to be sent out as a network card, you need to compare the sent packets and the client received the packet to determine whether the network card or routing and so on if the packet is cut.
8. When the chimney is turned on, the information of the captured packet may be incomplete, if the behavior of the packet is strange when the view, suspect drops, then be sure to ask the customer to close chimney (run cmd with Administrator and execute command: netsh interface tcp set global chimney=disabled) After the Netmon is collected again.
Next, let's discuss today's case. With the knowledge and skills of the above Netmon, the Netmon data received in this case is very pertinent. The client's Netmon data according to the port number filter, it is clear to see that the client has been facing the issue of focus. 16 is the retransmission of 12, 17 is also the retransmission of 12, even 18,19,20:
So let's take a look at 12 more information:
You can see that the length of this package is 4096. What about 16? Looking at 16 details we found that 16 of the length became 1460:
At the time of retransmission, the packet length becomes smaller, which means that the packet size is automatically resized due to the inability of the packet to be delivered. Continuing to view 17,18,19 and 20 will find that the lengths of these packages are all 1460. It is clear that this is a big package that does not pass in the past leading to retransmission problems.
So now the question comes, why the first big package is not sent over, after the small packet retransmission is not successful?
After viewing the server-side Netmon, this is due to the fact that the server side only received the last packet after the packet of transfer 4096 was cut into 1460+1460+1172. Next, because the sequence is broken, the server side will consider this an illegal package because the information in the middle is missing. The connection is then directly blocked on the server side (so the error message we receive is "connection forcibly close by the remote server
"), so the subsequent re-transmit 1460 of the package server has never been received (subsequent retransmissions all failed).
The solution to the final problem is to turn off the two options on the NIC: Jumbo packet and large Send offload (LSO).
Opening Jumbo packet indicates support for large packets, and closing indicates that packets emitted from the NIC take standard size 1500.
If the LSO is turned on, the packet will be completed by the NIC driver (NDIS). If the LSO is closed, the cut will be done by the operating system (TCP Stack) and cut to a size determined by the settings of the Jombo packet. We usually do not recommend the network card to cut the package, because the network card cutting packet is related to the environment, may cause the size of the package is not fixed, it is recommended that the operating system to cut the package. When these two options are turned off, the size of the transfer packet is 1500 of the standard size, which solves the problem of the large packet being cut and retransmission failing.
This is today's share, more SQL Server case studies keep an eye on this blog update.
Server-side Netmon and client-side Netmon Jumbo Packet: Jumbo Pack
SQL Server Connectivity Problem case resolution (1)
