Maximum Network packet size for SQL server 2005?

Question

Maximum Network packet size for SQL server 2005?

hardwaremister

SSChasing Mays

Points: 603
More actions
October 28, 2009 at 6:02 am

#227433

Hello,
I have been trying to look for some documentation on the maximum feasible packet size for SQLserver 2005 but I have came empty handed.
Does anyone know the limit?
Regards,
Francisco.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

I think packet size is a TCP/IP limitation; it's not really related to the program that sends the data(ie Word, SQL 2005, MYSQL,etc)

For Ethernet LAN/WAN, max packet size is 1500 octets.

http://en.allexperts.com/q/General-Networking-Lan-1049/TCP-IP-Packet-size.htm

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

george sibbald SSC Guru Points: 104210 More actions · Answer 2

there is a configuration option in SQL for network packet size, this has a maximum of 32K.

change this at your peril. I did once and it broke part of an application. Took ages to sort that one out.

http://msdn.microsoft.com/en-us/library/ms187866.aspx

---------------------------------------------------------------------

hardwaremister SSChasing Mays Points: 603 More actions · Answer 3

Lowell (10/28/2009)
I think packet size is a TCP/IP limitation; it's not really related to the program that sends the data(ie Word, SQL 2005, MYSQL,etc)
For Ethernet LAN/WAN, max packet size is 1500 octets.
http://en.allexperts.com/q/General-Networking-Lan-1049/TCP-IP-Packet-size.htm

That is, if you are not using Jumbo Packets which admit 4KB by norm and depending on the device can go much further (7KB on my nic, 32K on higher grade nics). 4Kb is secure for all devices, but Jumbo frames must be enabled on hardware drivers. Worst case scenario, upon sending a package that it is too big, the receiver's tcp stack will reject the package and the sender *should* try to "negociate" the frame by sending new packets with different frame size until one is finally ack'ed.

My question was more about the technical limits of SQL server.

hardwaremister SSChasing Mays Points: 603 More actions · Answer 4

george sibbald-364359 (10/28/2009)
there is a configuration option in SQL for network packet size, this has a maximum of 32K.
change this at your peril. I did once and it broke part of an application. Took ages to sort that one out.
http://msdn.microsoft.com/en-us/library/ms187866.aspx

Thank you. 😉

hardwaremister SSChasing Mays Points: 603 More actions · Answer 5

For the record, just in case anyone is looking for the same info:

There is a BUG in SQL Server 2005. It is documented that setting the packet size above 16KB will create problems with the creation of maintenance plans.

On a quick read, it looks like SaveToSQLServer method doesn't work for packets bigger than 16KB, so I wouldn't be surprised that the whole SQL Server Package Store doesn't work under those circumstances... so it might have a greater impact, not allowing the access/execution in the Package Store.

In SQL Server 2005, when setting the network packet size option to 16,388 or above, it is possible that maintenance plans will fail to run. Users may also be unable to save new maintenance plans, receiving an error similar to "OLE DB error code 0x80004005 (Communication link failure)" with the SaveToSQLServer method. Editing and loading of existing maintenance plans will fail with the message: Value cannot be null. Parameter name: component (System.Design).

george sibbald SSC Guru Points: 104210 More actions · Answer 6

cheers for posting that.

---------------------------------------------------------------------

jbuttery Ten Centuries Points: 1206 More actions · Answer 7

There may be some crossover among the different network layers here. Network vendors are partially responsible. Some use frame, packet or MTU interchangeably.

Ethernet is at the physical network layer and has a MTU (maximum transmission unit) of 1508 bytes or octets.

TCP/IP is at the higher protocol layer and I believe its definition does not limit frame size. The protocol has some overhead so TCP/IP over Ethernet typically can deliver a max of about 1400 data bytes per packet.

Jumbo Ethernet current implementations have a limit of about 9K bytes. Both hosts and all internetworking equipment must support it.

I think (but haven't found out yet) SQL Server max packet size refers to the data stream sent to the NIC.

I welcome any corrections or additional info.

Jim

jbuttery Ten Centuries Points: 1206 More actions · Answer 8

I forgot my standard caveat of testing and benchmarking thoroughly before and after any changes and prior to moving to production.