Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Have you considered not using named pipes? devproconnections.com/article/sql-server/… –  Aaron Bertrand Nov 22 '12 at 17:23
add comment

1 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.

share|improve this answer
    
I'd agree to everything except for the CPU Affinity settings statement. When you do that all you are doing is removing those cores from being available to SQL Server. There's nothing in Windows to tell it to always use those cores that SQL isn't using. Just give SQL all the cores and be done with it. –  mrdenny Dec 12 '12 at 5:20
    
@mrdenny Agreed that Windows may not use the CPUs not checked. And that is a really the important part of the solution for the reader to illustrate that keeping SQL from using ALL the CPUs when under stress will give relief to handle File/Network/other Service requests. A case in point, I had a server I could RDP to, but was very very slow because of high use. With these changes I was able to thereafter manage the server. It's now a standard part of my implementations and I haven't had this particular issue with my servers. –  SnapJag Dec 12 '12 at 5:42
    
Given how expensive CPU power is, I'd have a hard time justifying keeping 1 or two cores from processing production workload just in case I needed to RDP to the server. Especially given that I pretty much shouldn't ever need to RDP to a server to troubleshoot. –  mrdenny Dec 12 '12 at 6:43
    
A justification may be hard. I view it as insurance. On the dev box the developers reported slowness from a heavy loads. Upon investigation, there were heavy resources being consumed. Connections were no longer being accepted by the apps. Upon RDP to the box, we identified that this action alone should be quicker to connect. Making the above changes for CPU fixed the problem. It's not a "just in case I needed to RDP" scenario for troubleshooting. It allows the other networking services to still process instead of errors to connect from the application as I have seen too. –  SnapJag Jan 8 '13 at 22:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.