SQL Server Performance Tip: Favoring TCP/IP over Named Pipes

Most applications typically connect to Microsoft SQL Server through named pipes or TCP/IP connections. Although architectural decisions and code quality have a far greater impact on overall application performance when interacting with a SQL Server database, the type of network protocol that you choose can also play an important role.

TCP/IP vs. Named Pipes

For small to moderate amounts of database traffic on gigabit networks that aren't heavily loaded or saturated, the choice between TCP/IP and Named Pipes protocols typically won't matter too much. However, for situations where connectivity is strained or hampered by excessive load and additional routing overhead (such as with a VPN), protocol choice can have a very noticeable and tangible impact. In fact, I've seen several cases where switching Named Pipes connectivity to TCP/IP has resulted in tangible and dramatic performance improvements—especially in situations where client applications tend to be very chatty and are constantly streaming large commands and queries back and forth to the SQL Server database.

Furthermore, in cases where there's no noticeable difference between TCP/IP communications and Named Pipes interactions with the server on a well-tuned network, the reality is that Named Pipes connectivity always uses more packets to get the same amount of work done when communicating with remote clients. As such, remote client connectivity should almost always favor the use of TCP/IP communications over Named Pipes.

Named Pipes

In terms of raw speed, the Visual Interface Adapter (VIA) protocol is acknowledged as the fastest network library available for SQL Server client applications. However, VIA is an expensive protocol that's tied to dedicated hardware and is primarily used for high-end benchmarks. Likewise, it's hard to beat Shared Memory connectivity in terms of raw performance. However, the Shared Memory protocol has a huge limitation in that it works only in situations where the client and server can share the same logical memory. Because of this limitation, the protocol can be used only by clients on the same host or machine as SQL Server itself. As a result, the protocol's performance strengths are severely diminished, as it rarely makes sense to deploy applications on the same host or machine that's handling back-end SQL Server workloads.

Along these same lines, the Named Pipes protocol also offers high-performance benefits when used by client applications residing on the same box as the SQL Server database that's being accessed. That's because in this configuration Named Pipes connections run in kernel mode. But running applications and application servers on the same host as your database host is never a good idea when you're dealing with n-tier applications or situations that require your applications to scale.

Unlike the shared memory protocol, Named Pipes can also facilitate client connectivity to remote SQL Server databases or to SQL Server hosts on different machines. However, this is a problem because Named Pipes actually incurs significant overhead to manage flow control for remote connectivity over the network, which results in additional overhead and a higher number of packets. Accordingly, SQL Server Books Online (BOL) says the following:

For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.

Likewise, BOL also covers several significant benefits that TCP/IP enjoys over Named Pipes, and I therefore consistently recommend TCP/IP in virtually all situations in which remote connections are involved.

Forcing TCP/IP Connections

With this in mind, it's important to remember that there are several different ways to force applications to connect through TCP/IP instead of Named Pipes. First and foremost, disable the Named Pipes protocol on your SQL Server host. Obviously, this is something that you'll want to test or ease into production. Likewise, it's also possible to disable Named Pipes connectivity from your application hosts if you've got the correct Microsoft Data Access Components (MDAC) and libraries installed. In my experience, this approach is much more painful and problematic than other options.

A simple approach for forcing TCP/IP connections instead of Named Pipes connections is to use an IP address (instead of a host-name) as the data source within your connection strings, which forces the use of TCP/IP connectivity. However, this approach comes with the limitation of binding your applications to a hard-coded IP address, which might be fine for smaller applications or in a fairly static environment.

If you prefer to use machine names as a form of aliasing to maintain additional flexibility in terms of network access, then pre-pend "tcp:" to the front of your host names in the data source section of your connection string. For example, the following is a valid example of how to force client applications to use TCP/IP instead of Named Pipes:

Data Source=tcp:hostname;etc

Similarly, you can also use the following to force client applications to use TCP/IP connectivity:

server=tcp:hostname,portnumber;etc

Both of these examples use host names as a means of aliasing specific IP addresses.

Increased Performance with TCP/IP

Regardless of the mechanism that you use to force remote clients to connect to SQL Server through TCP/IP, the reality is that you should see less chatter with this protocol and you'll see much better performance in cases where VPNs or significant network load come into play. Again, the use of TCP/IP over Named Pipes isn’t some magical panacea that will remove all problems—but it's a better set of network libraries in almost all cases where network access to SQL Server is needed by your applications.