Network package size

We all know that network handling is very expensive, so when loading a large amount of data changing the network package size can improve the performance dramatically. By increasing the package size means that we will transport fewer but larger packages over the network.

Here are two common examples how to set the package size:

.Net Connection String
Server=MyServer;Integrated Security=SSPI;Packet Size=32767

SSIS Connection Manager Configuration

Packet size may be a value in the range of 512 and 32767 bytes. An exception is generated if the value is outside this range.

Checking the network package size from inside SQL Server
One thing is the initial configuration of applications and SSIS jobs, another thing that I find more important is how to get this kind of information from SQL Server. I my daily work as a SQL Server performance tuner it is not that unusual that the only server I can access is the SQL Server. Of course the nice guys at Microsoft have provided us with a DMV that displays that information to us. The DMV is called - sys.dm_exec_connections – here is the link to the documentation - http://msdn.microsoft.com/en-us/library/ms181509.aspx

To save you some time I have written a query that returns the information that this blog is about:

<span style="color:blue;">select
 </span>session_id<span style="color:gray;">, </span>net_transport<span style="color:gray;">, </span>net_packet_size
<span style="color:blue;">from </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">dm_exec_connections
</span>

And here is a screen shot of the output

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.