Issues to consider when changing the default value of network packet size for SQL server connections

Article translations Article translations
Close Close
Article ID: 2008195 - View products that this article applies to.
Expand all | Collapse all

Summary

The network packet size configuration option in SQL Server is used to set the package size (in bytes) to be used when communicating with SQL server. The size can be set on the server side as well as on some of the client side libraries. The default packet size set by Microsoft SQL Server is 4,096 bytes. This article discusses some of the issues that you need to be aware of when making changes to this setting.

As mentioned in the following note in Books Online topic on this option, we recommend that you do not change this value unless you are certain that it will improve performance.

Note:

Do not change the packet size unless you are certain that it will improve performance. For most applications, the default packet size is best.

The network packet size on the server side can be configured using network packet size configuration option.

For more information on how to control this setting from a client net library refer to the following table:

 

Client library

Option

Default

SQL native client

SSPROP_INIT_PACKETSIZE

0 (use server side)

System.data.Sqlclient

PacketSize                         

8000

ODBC

SQL_ATTR_PACKET_SIZE

Use server side

SQLOLEDB

SSPROP_INIT_PACKETSIZE         

4096                      

JDBC

setPacketSize(int packetSize)

8000

Note: The current documentation for Sqlclient incorrectly states the default Packet Size for Sqlclient connections as 8192. This will be corrected in a future refresh of Books Online.

The network packet size of a client connection can be determined by monitoring the Audit Login event or the ExistingConnection event in SQL Profiler. The Integer Data data column contains the network packet size.

More Information

The various issues that you need to be aware of when changing the network packet size are documented below:

  • SSL and TLS limitation: Secure Socket Layer (SSL) and its replacement, Transport Layer Security(TLS), limit data fragments to 16k in size. This is documented in the public RFC 2246 (section 6.2.2) and the current implementation of SQL Network Interface (SNI) layer adheres to this specification. Using a network packet size that is greater than 16K is not supported in these environments. This limitation affects the following scenarios:
    • SSIS Packages and Data Collector: For further information refer to the following KB article:
      • 2006769: Communication link failure’ error message is reported for SSIS packages on SQL servers configured to use encryption and a large network packet size
         
  •  SQL Server 2000 and 2005 environments: Please refer to the following KB article for further information on a known issue in these environments
    • 903002 A "Failed to reserve contiguous memory" error message may be logged in the SQL Server error log when users connect to an instance of SQL Server by using a network packet size that is larger than 8,060 bytes. 
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2008195 - Last Review: January 14, 2010 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
KB2008195

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com