Fastest SQL Server protocol?

Question

What is the fastest SQL Server connection protocol?

Related: which protocols are available remote versus local, and does that affect the choice of fastest protocol?

Answer 1

Shared memory is fastest for local (client and server on same machine). Named pipes is probably 2nd fasted for local. For remote everyone is using TCP-IP and the remaining protocols are kind of turning into networking history.

Answer 2

Using Shared Memory Protocol

The network libraries you choose when installing SQL Server can affect the speed of communications between the server and its clients. Of the three key network libraries, TCP/IP is the fastest and Multi-Protocol is the slowest. Because of the speed advantage, you will want to use TCP/IP on both your servers and clients. Also, don't install unused network libraries on the server, as they only contribute unnecessary overhead**

Answer 3

Named Pipes is the fastest SQL Server protocol.

Answer 4

VIA. This is the fastest SQL Protocol, it runs on dedicated hardware and is used in doing SQL Server benchmarked records.

Shared Memory is next as performance, but it only works between a client and a server that can actually share memory, so local only.

For remote connectivity on ordinary hardware, TCP is the way to go. Under normal operations, it has the same performance as Named Pipes. On slow or busy networks, it outperforms NP in robustness and speed, a fact documented in MSDN:

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.

Named Pipes also can lead to client connect time outs:

TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.

Unfortunately the normal client configuration tries NP first and this can cause connectivity problems (for the reasons cited above), where enforcing TCP on client network config (or in connection string, via tcp:servername) skips the NP connect attempt and goes straight to TCP for a much better experience under load.

Now is true that the same link I quoted above goes on to praise NP for its easy of configuration, most likely referring to no need to open SQL TCP port in firewall, but is there where me and BOL have different views.