Named pipes versus TCPIP
-
Hi,
I was looking at the SPIDs on my SQL Server 2000 server and noticed that while the majority of connections were using TCP/IP, three connections were using Named Pipes. The users were all in the same office where the server is situated, and none of them were reporting any particular performance problems.
All the users on the system connect using the same application configuration from the network, and all their machines were running TCP/IP, so I was curious as to where the decision on the connection type was made, and what criteria were used. I am guessing that the SQL server browser service makes some sort of judgement as to which connection type is going to give the best performance?
PS: Oops just realised I have posted this in the wrong SQL Server version
David
If it ain't broke, don't fix it...
-
Having chased this down receintly, the fault is Microsoft.
Microsoft keeps breaking ADO (MDAC) and now NativeClient (SQL 2005) by changing how the default Network Library is chosen. If you look at most new "connection" tools the defaults keep resorting to NamedPipes and almost NO ONE uses NamedPipes.
The easy answer is to add the ";Network Library=dbmssocn" (without the quotes) to your connection string for ADO, ADO.NET or NativeClient. Or for ODBC use ";Network=dbmssocn" (without the quotes).
For all you ever wanted to know about Connection strings and network libaries for SQL Server:
http://www.connectionstrings.com/article.aspx?article=howtodefinewichnetworkprotocoltouse
Andy
-
Thanks David, that's interesting. I will try your suggestions. I might also monitor the connections to see if the same machines use Named Pipes all the time, and then have a look at the version of MDAC they are running.
David
If it ain't broke, don't fix it...
-
The decision to connect with Named Pipes or TCP is always made by the client.
For short network pathways, there is not much performance difference either way. If your clients have a WAN between tham and SQL, then Named Pipes is inherently slower. There is a good discussion of this in SQL 2005 BOL - the principals around NP and TCP performance apply to any SQL version and to non-SQL applications.
Windows Copy over a WAN is horribly slow. This is because when Copy was written NP was the main protocol, and Copy is still using it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply