Differences in Default Network Packet Size: SqlConnection vs. SQL Server defaults

Question

I noticed yesterday that there is a noticeable difference between the default network packet size in .NET's SqlConnection class and the default in SQL Server itself.

For the SqlConnection class' PacketSize property, per this link:

The size (in bytes) of network packets. The default value is 8000.

In previous versions of that article, it mentions (all the way back to .NET 1.1) that the default was 8192 bytes. For SQL Server, however (per this link):

The default packet size is 4,096 bytes.

From that article it appears that that's been the default since at least SQL Server 2005. Does anyone know the reason for this difference between them?

UPDATE: To add more fun to this question, I've been talking to Thomas LaRock (who happens to be a SQL MVP), and he mentioned to me that in SQL Server 2000 the default network packet size was, in fact, 8192 bytes (like .NET 1.1!). It got changed in later versions, though, because of how much fragmentation it was causing, necessitating weekly reboots of the server.

So why did SQL Server reduce it by half, but .NET only reduced it by 192 bytes? Is there something forcing SQL Server to use only multiples of 2 for this?

Answer 1

This has always been the case. Even in SQL 7.0 and SQL Server 2000, the default value was 4096 bytes.

The problem happens when you try to use a higher network memory than 8060 bytes as it will require SQL Server to allocate memory from a region of memory that uses a size of 384 MB.

Its very easy to modify the packet size from the client application side. In connection string, you just set the packet size=4096.

Its advisable not to fiddle with the default network size on a server running SQL Server, unless you are doing some heavy lifting using SSIS or your application is performing bulk copy operations, sending and receiving BLOB data.

Note that SybaseASE uses 512 bytes as default packet size, so SQL Server 6.5 might have that set as default (but I have no reference to cite if that was the case for SQL 6.5).

Refer to : Network Packet Size: to Fiddle With or Not to Fiddle With