sql server connect: Network Packet Size -- sql server connect

Network Packet Size

SteveM
11/28/2005 11:22:29 AM

sql server connect: I'm curious about KB article (903002).
http://support.microsoft.com/default.aspx?scid=kb;en-us;903002

In this KB, it says that users that connect with a network packet size
greater than 8060, that it requires SQL Server to allocate memory from
a region of memory that uses a size of 384 MB. What does this 384 MB
take away from? Performance impact?

This is followed by a note that "By default, the Microsoft .NET
Framework Data Provider for SQL Server sets the network packet size to
8,192 bytes."

After checking our .NET applications, all use the default of 8192. We
don't get this error message very often as mentioned in the KB article,
so not really a problem.

However, I'm curious if there has been any papers / testing on changing
this .NET setting lower (to 4096) as far as performance. Any idea on
why .NET sets this to a default of 8192?

Does anyone know if this behavior in SQL Server 2000 is the same as SQL
Server 2005 or has it changed?

I'm considering changing the .NET applications to use 4096, but wonder
if it buys me much?

Re: Network Packet Size

Andrew J. Kelly
11/28/2005 5:24:32 PM

I believe the reason why it defaults to 8K is that is the smallest unit that
SQL Server will use for reads and writes. It will be at the page or higher
level. In regards to the 384MB which is the MemToLeave area you are
referring to that can have a significant effect. This is a limited resource
and to be effective must have lots of contiguous free memory. By using this
for packets you can effectively fragment this space. But as most things in
SQL Server your mileage may vary as there are a lot of factors that
determine overall performance.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]