Tuesday, August 3, 2010

SQL Server & Network Packet Size

I've seen a number of references to packet size on my research into optimising SQL & SSIS.
The default packet size is 512 Bytes and the maximum SQL server supports is 32767.

So, the question is, " Why can't i just change it ? "

Hardware support is the answer
Connecting clients and the network switches need to support the packet size (e.g. 9000 for Jumbo frames)
Network Cards need to support the speed (and for that support to be enabled via the drivers in Windows)

Your OS also needs to support it. In the case of Hyper-V (pre Windows 2008 R2), the virtual NIC cannot support Jumbo frames.

If your environment is suitable however, here's the configuration code to do it >

EXEC SP_CONFIGURE 'show advanced option', '1'; 
RECONFIGURE;
EXEC SP_CONFIGURE;

EXEC SP_CONFIGURE 'network packet size (B)', '9000';
RECONFIGURE WITH OVERRIDE;
RECONFIGURE;

EXEC SP_CONFIGURE

Link : Gigabit Ethernet Jumbo Frames

No comments: