A network pipeline isn’t nearly as pleasant to look at as the oil pipeline (or anything) in Alaska, but it’s something that DBAs should be aware of. There is a server configuration in SQL Server that controls the size of packet in which SQL Server sends out data. This setting is called network packet size (B). Yet another size we need to know about in SQL Server.

As DBAs, we tend to think of size in terms of 8K pages and 64K extents or in terms of how many GBs of storage we need. Rarely do we think of something so small that it’s still measured in bytes. The default network packet size in SQL Server is 4096 bytes or 4KB. There are recommendations out there to make this value larger for scenarios where network is a bottleneck, but very little guidance on knowing when to make this change or whether we should. I suspect that very little is known about the impact of increasing this value. Let’s investigate.

Network Packet Size and Maximum Transmission Unit

As I stated above, network packet size is the size of the packet sent out by SQL Server. That needs to be correlated to the maximum transmission unit (MTU) of the network path from the host server to the end recipient. The key here is that if you increase the network packet size above the maximum transmission unit, it could hurt performance. If the MTU of the network is a smaller size, then the packets being sent out have to be broken down into smaller packets. This adds overhead in the network and can even cause smaller packets sent behind the larger packets to be delayed.

Let’s do a quick test with ping from the command line. The following test will be performed on my laptop by simply pinging my wireless router. The MTU of my wireless network is 1500 bytes which is smaller than the default network packet size so I already know that some packet breakup will occur for external connections to my laptop. For my ping test, I am going to use the -l switch to tell it the size of packet to send. The ping itself has a 28 byte overhead, so I subtract 28 bytes from the size I specify to get the appropriate size. Below are the results of a ping test with the MTU (1500 bytes) of my network, the default network packet size (4096 bytes), and the maximum network packet size (32767 bytes) to see the difference.

Notice the difference in time? Average round trip goes from 4ms to 6ms to 16ms. Sending larger packets of data can be slower, per packet, than smaller ones, particularly when there is additional overhead from having to break down the packet size. Once the packets reach the network, they are going at the speed of the smaller packet, so nearly all of the above difference in round trip time can be attributed to breaking down the packets into smaller packets.

Determining Maximum Transmission Unit

There is a chart of some well known MTUs here: Wikipedia: Maximum transmission unit.

But let’s assume that you don’t know the MTU of every hop and every piece of hardware between your SQL Server and the destination machine. Your network connection may be a 10Gb (gigabit) connection, but that doesn’t mean it is for the whole path. We can determine the MTU using the ping test. For this test, we will use the –-f switch to mark the packets as “don’t fragment”. This means the packets will not get broken down into smaller packets. They will be rejected. This is an especially good test for cases where there is a known destination like a web server. If you are planning to increase the packet size, you should make sure that the network between the two servers support it. for my examples below, I’m going to continue to ping my network router, but if this was a real scenario, I would ping my web server’s IP Address.

First, I test with the maximum network packet size, and I see that the ping attempts all result in lost packets due to the packets being marked to not be fragmented:

If I repeat the test while reducing the size by 1/2 until I reach a size that doesn’t fail, I eventually end up at a size of 1024 bytes:

Then by making small incremental changes, I can finally find the point at where it fails.

In the above, we can see that the largest packet I could send without fragmentation is 1500 bytes (1472 + the ping overhead of 28 bytes). Remember that this was on a wireless home network. Your mileage will hopefully vary … a lot.

Update

Friend and fellow SQL professional (Microsoft PFE) Thomas Stringer (blog|@SQLife) sent me the following PowerShell script that he wrote to figure out the current MTU:

Save this script as PowerShell script and run it. I added a check for argument -Q that will suppress the intermediate messages and only output the final MTU value. Call it with -Q to run it in quiet mode.

^*Reposted with permission from SQLSoldier.com.