THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL Server and Jumbo Frames

During the 24 hours of PASS we (Thomas LaRock (Blog/Twitter), Argenis Fernandez(Blog/Twitter) and myself) got into a discussion about enabling Jumbo Frames (network packets over 1500 bytes, generally set to 9000 bytes) for use with SQL Server.  I was surprised that most people on Tom's session and then later again on Twitter hadn't heard about Jumbo Frames, and I have since been asked a number of questions about the subject.  Now I will admit that I am not a networking expert up front, but since I've been asked quite a few questions since bringing this topic up, I thought I'd post a quick blog post on the subject with some references as well. 

The topic of Jumbo Frames first came to my attention when I read a blog post on the SQL Server Performance blog titled ETL World Record.  It was then mentioned by the SQLCAT team in March 2008 in a post titled Backup More Than 1GB per Second Using SQL 2008 Backup Compression.  Additionally a SQL Server Checklist posted by John Hicks mentioned it a day later, so I began looking into it.  There is a decent write up on Wikipedia that explains what Jumbo Frames are, and why the default network packet was set to 1518 bytes.  There are also some interesting external references listed on that page as well, including some older (circa 1999) studies of performance differences. 

The SQLCAT team has mentioned Jumbo Frames in a few of their whitepapers including Top 10 SQL Server Integration Services Best Practices, Backup More Than 1GB per Second Using SQL 2008 Backup Compression, and most recently A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network.  It is also covered in the information in the Books Online for using the Microsoft iSCSI Initiator (which I use for my home built testing cluster):

Use Jumbo Frames if your network infrastructure supports them. Jumbo Frames can be used to allow more data to be transferred with each Ethernet transaction and reduce the number of frames. This larger frame size reduces the overhead on your servers and iSCSI targets. For end-to-end support, each device in the network needs to support Jumbo Frames, including the network adapter and Ethernet switches.

So what do you need to enable Jumbo Frames?  First the network cards on the SQL Server as well as any computer/server connecting to the SQL Server would need to have Gigabit or higher network cards installed.  In addition, any network switches between them would also have to be Gigabit or higher.  Then it is recommended that you use appropriate CAT-6/CAT-7 cabling between the devices, especially for 10 gigabit connections over longer distances.  Then the switches and network cards have to be configured to allow/use Jumbo Frames/MTU, generally set to 9000 bytes.

Based on the CAT Team recommendations, I made the change last year on the servers that could support it, and the app servers were also updated.  Unfortunately I don't have performance information from before changes were made to the servers in my environment where Jumbo Frames were possible and enabled so I can't provide information as to the benefit associated with enabling this.  However, by reducing the need to fragment packets over 1500 bytes and the reassemble them, there should be a measurable improvement in data throughput.  I don't have an environment at this point where I can flip flop settings and run benchmarks to measure it, or I would.  I like to think that it improved things on those servers, but I have no empirical evidence.

General wisdom amongst additional people I’ve discussed this with holds that for standard OLTP only systems, this won’t offer much benefit because the data packets will generally be smaller than the 1500 bytes anyway.  However, for instances where large volumes of data transition between servers, reporting and OLAP for example, enabling Jumbo Frames will be beneficial.  One thing to be aware of is that if you use a connection that has a network packet size larger than 8000 bytes, it will use multi-page allocations which can be problematic on 32 bit SQL Servers due to limited VAS.

So where exactly might you use Jumbo Frames with SQL Server?

  1. Dedicated Backup Network
  2. Bulk Loading Data from one server to another
  3. Dedicated database mirroring, log shipping, or other replication topology network where all servers and interconnecting hardware support jumbo frames.
  4. Segmented Web DMZ environment where the Web Servers communicate with SQL Server on a dedicated network

Where wouldn’t you use Jumbo Frames with SQL Server?

  1. If the SQL Server is client server based where Windows Client OS's (Vista/XP) connect to the database server directly
  2. If the networking hardware end-to-end doesn't support Jumbo Frames they should not be used.
  3. Optimized OLTP workloads with small data packets won't see much added benefit from making this kind of change.

In addition, if you enable Jumbo Frames, consider setting the EnablePMTUBHDetect registry key to 1 so that Black Hole Detection occurs and the system auto-switches down to a lower MSS (536) to prevent packet loss.  Keep in mind that with this downswitching the packet size is now 1/3 of the original 1500 value so you would have been better off not using Jumbo Frames at all.  However, the lower MSS is still better than having packets lost due to failed down switch of the packet size.

Published Monday, October 12, 2009 2:09 PM by Jonathan Kehayias

Comments

 

Remus Rusanu said:

Mirroring and SSB traffic also benefits greatly from jumbo frames. The only downside of jumbo frames is that they have to be supported end-to-end, routers cannot convert in-flight to regular 1500 bytes frames.

October 12, 2009 5:30 PM
 

Perry Whittle said:

Generally you want to disable this on your NICS as it can have an adverse effect on performance. I think the jury is still out on this one and i peronally have seen a few recognised issues (especially with iSCSI connections) when this is enabled

Regards

Perry

October 25, 2009 2:42 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement