Sql-server – Named pipes connection closed when subjected to load

errorsloadmax-connectionssql server

We're hosting a service where we have a messaging service. When a message arrives, it checks which subscribers it should be sent to by connecting to the database, and querying the subscriptions table.

A few months ago we started seeing the following error when the message service tries to connect:

An error has occurred while establishing a connection to the server.

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 5)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 1326)

We were at that point running on virtual servers hosting SQL Server 2008, and we were moving to a clustered SQL Server solution on physical hardware.

After the move to the physical hardware the error dissapeared, so we put it down to having something to do with the virtual servesr. However, now it is back.

It seems that the error occurs when there is high load.

Has anyone experienced why this happens? Does anyone have any experience of solving it?

Best Answer

What do you consider to be high-traffic?

Turn off Named Pipes and turn on TCPIP It's lighter in it's communication. In my SQL installations, I never use Named Pipes except if the Application is on the same machine as the SQL service, and that's really ... never ... I always separate the application and SQL Server because monitoring and performance measures are more difficult to obtain and decipher.

Separate Applications/services from SQL Server Also, if you have SQL and your application on the same server, put them on different servers from each other so that the SQL server is dedicated.

Index and stats maintenance Make sure your indexes and stats are all maintained. Sometimes the activity can be high enough when stats and indexes are stale or fragmented that this in and of itself consumes most resources. Especially with standard installs and with no adjustment to CPU and RAM pinnings (see next item).

Pinning CPU/RAM to settings so OS/Networks/Services are not entirely consumed by SQL

  1. update the CPU Affinity to use all but 1 or 2 cpus (if box has 8 cpus, use 6 or 7 for SQL and leavning 1 or 2 unchecked for the OS/Network/Services to use)
  2. set the MIN and MAX RAM for the SQL Server to be "pinned" at the same RAM setting (if box has 8GB or RAM, set MIN to 6144KB and MAX to 6144KB), or something where MIN=(MAX Machine RAM * 20%) or MAX=(MAX Machine RAM * 10%) rounded to multiples of 1024KB.

These last two settings help to make sure that the resources for the SQL are not entirely consumed by SQL and the OS and Network and other services can respond and perform their standard requests. I've had situations where so much activity was happening (1K of simultaneous users, all indexes, stats, etc were good) that the box resources were completely consumed by SQL and the network services reported that the box could not be reached. Once I made these changes, and have been doing this ever since, many of my resource-consuming problems have gone away.

You could even try these last two with Named Pipes on and it should work because the Network can respond with the high traffic. But I would recommend TCPIP instead.

Related Question