blob and network packet size

1) For performance of BLOB only data that will have at least 100 KB per BLOB,
what is the best network packet size setting? The resource kit indicates that
it is 8080. Is that correct, or is it a typo that should say 8192?
2) Does the client connection have to match the network packet size setting
for it to be used?
In general the network packet size setting should be left alone, it is not
going to give you much one way or another in terms of performance.
The main reason I say this is everything has to go across TCP-IP, which has
a limit of ~1430 bytes per packet. So the TDS packet size is not the
limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
the end.
Another problem with cranking it up is that it causes SQL Server to also
chew up more memory holding larger packets, reducing scalability. So 4K
default is good. Network packet size > 8000 bytes should be avoided for
server scalability reasons, this causes SQL to allocate packets out of
mem2leave area instead of normal buffer pool.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"dp" <dp@.discussions.microsoft.com> wrote in message
news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
> 1) For performance of BLOB only data that will have at least 100 KB per
> BLOB,
> what is the best network packet size setting? The resource kit indicates
> that
> it is 8080. Is that correct, or is it a typo that should say 8192?
> 2) Does the client connection have to match the network packet size
> setting
> for it to be used?
>
>
|||Your reply make me quite confused. The following are from Chapter 11 - Using
BLOBs in SQL Server 2000 Resource Kit. url:
http://www.microsoft.com/technet/pro...rt3/c1161.mspx
If your database application sends or receives large amounts of BLOB data,
consider changing the packet size of the chunks of data that transfer
requests and results between clients and servers. To change or set the packet
size, use the network packet size option in Configuration Options. A packet
size larger than the 4,096-byte default may improve efficiency by lowering
the network reads and writes. A packet size of 8,080 bytes is best for
performance in SQL Server 2000 and SQL Server 7.0.
So which configuration should I follow? From your reply, tha statement above
is not quite correct, isn't it?
Anyway, would you (or someone) mind give me some direction on how to
configure a sql server 2000 which only hold blob data for best performance?
thanks much,
dp
m
"Matt Neerincx [MS]" wrote:

> In general the network packet size setting should be left alone, it is not
> going to give you much one way or another in terms of performance.
> The main reason I say this is everything has to go across TCP-IP, which has
> a limit of ~1430 bytes per packet. So the TDS packet size is not the
> limiting factor. Everything is squeezed down into ~1430 byte TCP packet in
> the end.
> Another problem with cranking it up is that it causes SQL Server to also
> chew up more memory holding larger packets, reducing scalability. So 4K
> default is good. Network packet size > 8000 bytes should be avoided for
> server scalability reasons, this causes SQL to allocate packets out of
> mem2leave area instead of normal buffer pool.
> Matt Neerincx [MSFT]
> This posting is provided "AS IS", with no warranties, and confers no rights.
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
> "dp" <dp@.discussions.microsoft.com> wrote in message
> news:1DAAA61D-5512-45A1-83F4-BAD4EF1CDDB7@.microsoft.com...
>
>