This article is part of a series of 5 where I am talking about the Microsoft Azure network bandwidth. For a better understanding please make sure you read also the other parts:
- What does Microsoft mean by low / moderate / high / very high / extremely high Azure network bandwidth (part 1)
- The isolated network setup (the environment used for network analysis) (part 2)
- What happens when we perform a PING? (part 2.1)
- What happens when we perform a PING in size of 4086 bytes? (part 2.2)
- Now let’s see what exactly happens when a file is copied over the network (file share) (part 2.3)
- Now let’s see what exactly happens when MS SQL traffic is performed (part 2.4)
- Now let’s see what exactly happens when IIS HTTP/HTTPS traffic is performed (part 2.5)
- The IOmeter benchmark tests who reproduce as close as possible the HTTP/HTTPS, SMB and MS SQL network traffic (part 3)
- The Azure Virtual Machines used to run the IOmeter benchmarks (part 4)
- Results and interpretations
Now let’s see what exactly happens when MS SQL traffic is performed.
The MS SQL network traffic is not that complex, but I will explain in advance what you will see in the next Wireshark and Process Monitor print screens.
In a non-academic explanation: MS SQL is by default configured to use 4096 bytes as maximum size for a network packet. So, when MSSQL server is sending the data to the network adapter is actually sending chunks up to 4 KB (4096 bytes) of data.
Let’s pause a bit the explanation and mention Large Segment Offload (LSO) – which “is a technique for increasing outbound throughput of high-bandwidth network connections by reducing CPU overhead. It works by queuing up large buffers and letting the network interface card (NIC) split them into separate packets.”
Continuing the MS SQL explanation: actually those MS SQL small chunks (up to 4 KB each) are sent over the network in much smaller size packets. The size of those packets is in direct relation with the Maximum Transmission Unit size.
Let’s pause again the explanation and mention in the absence of Large Segment Offload (LSO) (TSO / TCP Chimney Offload) the data MS SQL is sending over the network will be directly divided to packets in size with the Maximum Transmission Unit.On the other “end of the wire”, the system who is MS SQL client will receive network packets who will not exceed the Maximum Transmission Unit size.
The purpose of this explanation is to help reproduce as close as possible the MS SQL network traffic using IOmeter.
Of course, the way the data is handled (processed) before being passed to the network interface card has its performance implications – but this is a different subject. Remember, we look to benchmark only the network. We will try in our analysis to have the minimum influence from CPU, RAM and DISK point of view.
Here I will provide couple of important links for those who want to have a deeper reading in MS SQL, Process Monitor, Wireshark, LSO / TSO / TCP Chimney Offload, MTU
https://en.wikipedia.org/wiki/Maximum_transmission_unit
https://en.wikipedia.org/wiki/Protocol_data_unit
https://en.wikipedia.org/wiki/Network_layer
https://ask.wireshark.org/questions/24699/tcp-packet-length-was-much-greater-than-mtu
http://sqlsoldier.net/wp/sqlserver/networkpacketsizetofiddlewithornottofiddlewith
https://en.wikipedia.org/wiki/Large_segment_offload
http://www.peerwisdom.org/2013/04/25/disabling-large-send-offload-windows/
https://en.wikipedia.org/wiki/IP_fragmentation
The Advanced MS SQL server properties: the Network Packet Size is set by default to 4096 bytes.
The clients can also control the Network packet size. In the SQL Server Management Studio this can be done through the Connection Properties tab.
For .NET clients this connectivity parameter can be controlled via PacketSize Property of the SqlConnection class.
In this first MS SQL query & response network capture, I started with a T-SQL query who I knew will generate a small response (from data amount point of view).
The Wireshark network capture performed on the client side, indicates frame no. 1 is the MS SQL client request sent to the server.
As a proof we can convert the data section of this frame to Printable Text (String).
If we paste the content to Notepad … we will see exactly the T-SQL query we sent to the server. 🙂
As a comment with no reference to this article – the MS SQL traffic (T-SQL requests and responses) can be easily captured over the network and converted to non-binary format. If this is a security concern, then MS SQL provides the possibility to encrypt the communication channel with its clients, but it requires additional configuration in order to achieve this.
The Wireshark network capture performed on the client side, indicates the frame no. 2 represents the response the MS SQL server provided to the client.
And the Wireshark network capture performed on the client side, indicates the frame no. 3 represents the Acknowledge the MS SQL client sent back to the server to confirm the response was received.
The Wireshark network capture performed on the server side shows pretty much the same thing we saw in the Wireshark network capture performed client side.
Now, let’s do the same thing, but this time for larger sets of MS SQL data.
This query is actually extracting around 14 MB of data from the AllDocs table part of a SharePoint content database.
The capture performed using Process Monitor and Wireshark on the server side indicates the maximum size of the sent packets from MS SQL server are in size of 4096 bytes.
The Wireshark network capture performed on the client side, indicates that maximum size of the received packets from MS SQL server is not exceeding the default Ethernet MTU (1500 bytes).
Please note the capture is sorted based on the maximum size of the frames.
So, let’s make a short summary of what we’ve seen so far about MS SQL network traffic usage:
- the MS SQL server is by default configured to use 4096 bytes as maximum size for each network packet sent to the network interface card.
- the MS SQL clients are also configurable in terms of the maximum network packet sent to the network interface card (e.g.: by default SSMS is using 4096 bytes as network packet size).
- each 4 KB data chunks are actually sent over the network in much smaller size packets (the size of those packets is influenced by the MTU size).
- on the other “end of the wire”, the system who is MS SQL client, will receive network packets who will not exceed the Maximum Transmission Unit size.
Again, this explanation will help reproduce as close as possible the MS SQL network traffic using IOmeter.