Slow fetches and network buffer size

14 May 2005 4:31 PM

andsm

I have SP which returns about 4.5 kb of data. Network buffer siize parameter
on SQL Server is 4 kb. Fetch time for the SP is ~100-200 ms. If call same SP
from same computer where located SQL Server, total execution time is 30 ms.
If increase network buffer size up to 8 kb, total execution size for calls
from remote computer also 30 ms. Why it is and what can be done to increase
speed of fetches?

14 May 2005 7:18 PM

John Bell

Hi

You may want to check the queue lengths on the NIC (both server and client)
to see if they are overloaded. Increasing the number of NICs and/or network
speed will help. Make sure that all hubs/routers are functioning properly.
Retrieve less data! Increasing the network packet size will reduce the
number of network reads/writes and in this case make it more efficient.

John
Show quote

"andsm" <an***@discussions.microsoft.com> wrote in message
news:AF8886FC-0DF3-4EC8-B806-AD62438E2B5C@microsoft.com...
>I have SP which returns about 4.5 kb of data. Network buffer siize
>parameter
> on SQL Server is 4 kb. Fetch time for the SP is ~100-200 ms. If call same
> SP
> from same computer where located SQL Server, total execution time is 30
> ms.
> If increase network buffer size up to 8 kb, total execution size for calls
> from remote computer also 30 ms. Why it is and what can be done to
> increase
> speed of fetches?

14 May 2005 8:50 PM

Mike Epprecht (SQL MVP)

And don't forget, the SQL Server TDS Stream, may it be 4 or 8 or xx kb,
still gets encapsulated in TCP/IP packets. Your payload in those packets are
about 1.2kb (1.5kb total packet size), so no matter what, the TCP/IP stack
carves the SQL Server TDS packet up.

A re-transmit does slow down if a packet is lost.

A good way to tell if you have serious network issues is to copy a large
(200Mb or larger) file from the server to the client, and visa versa. See
how that performs against other networks.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:eU2GlmLWFHA.3076@TK2MSFTNGP12.phx.gbl...
> Hi
>
> You may want to check the queue lengths on the NIC (both server and
> client) to see if they are overloaded. Increasing the number of NICs
> and/or network speed will help. Make sure that all hubs/routers are
> functioning properly. Retrieve less data! Increasing the network packet
> size will reduce the number of network reads/writes and in this case make
> it more efficient.
>
> John
> "andsm" <an***@discussions.microsoft.com> wrote in message
> news:AF8886FC-0DF3-4EC8-B806-AD62438E2B5C@microsoft.com...
>>I have SP which returns about 4.5 kb of data. Network buffer siize
>>parameter
>> on SQL Server is 4 kb. Fetch time for the SP is ~100-200 ms. If call same
>> SP
>> from same computer where located SQL Server, total execution time is 30
>> ms.
>> If increase network buffer size up to 8 kb, total execution size for
>> calls
>> from remote computer also 30 ms. Why it is and what can be done to
>> increase
>> speed of fetches?
>
>