Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

Slow Connections with Sql Server


:P
On this page:

Argh... just fought with a small issue where connections to SQL Server were very slow on a new development box. Everytime I make a new SQL Connection there's a 2 second delay for the connection to occur. It's not only the first request, but any connection request including what should otherwise be pooled connections.

As you might imagine in Web applications that's a major problem even on a dev machine - it makes for some excruciatingly slow Web requests.

This is a local dev machine and I have a local SQL Server Developer installed.

TCP/IP is Disabled By Default

It turns out that a new SQL Server installation does not enable TCP/IP by default and if you're using a standard connection string it uses named pipes which on this machine at least results in very slow connection times with what looks like a 2 second delay for every connection.

I'm not sure why Named Pipes (or is it Shared Memory) are so dreadfully slow - if that's the case why would you ever use it?

Enabling TCP/IP

To enable TCP/IP we'll need to set the protocols in the Sql Server Configuration Manager. With recent versions of SQL Server it looks like Microsoft is no longer installing shortcuts for the SQL Server Configuration Manager, so I had to launch it using the mmc.exe and adding the snap-in:

  • Start mmc.exe from the Windows Run Box
  • Add the Sql Server Configuration snap-in (ctrl-m -> Sql Server)

Once you're there navigate to the Sql Server Network configuration and enable TCP/IP:

Just for good measure I also turned off the other two, but that's optional.

Why are Named Pipe Connections so slow?

I'm pretty sure that I've used Named Pipes in the past and didn't see this type of slow connection times, but I've verified this on several machines so it's not just a fluke with my new dev box. Each machine I've removed TCP/IP from takes about 2 seconds to connect with either Named Pipes or Shared Memory, while with TCP/IP Connections enabled connections are nearly instant on a local machine. As well it should be.

Anybody have any ideas why Named Pipes are so slow for SQL connections? It almost seems like a fixed delay because it's so consistent across several different machines.

After a bit more digging it appears that the problem isn't the Named Pipe Connection itself, but something related to the protocol discovery. In particular I ran into this problem with a FoxPro application which means it's using the SQL Server ODBC driver.

There connecting to SQL server like this with only Named Pipes enabled:

losql = CREATEOBJECT("wwSql")
? loSql.Connect("server=.;database=webstore;integrated security=yes")

results in the 2 second+ delay.

Changing the connection string to explicitly specify the protocol however fixes the issue:

losql = CREATEOBJECT("wwSql")
? loSql.Connect("server=np:.;database=webstore;integrated security=yes")

which seems to suggest there's some sort of protocol discovery problem where the driver is trying to use TCP/IP first, and failing to get a connection before trying the other protocols.

Interestingly the behavior would vary depending on the version of the SQL Server driver. Version 11 (which is what my ODBC driver is pegged to) exhibits this behavior, while the latest v13 does not.

This isn't a solution, but should be useful as a troubleshooting aid. It's easy to find out if a specific protocol is working as using a protocol that's not installed won't automatically try to go through all the protocols. Perhaps it's a good idea to be explicit about protocols (ie. specify tcp:. for the server explicitly always) because then if tcp/ip is not enabled you know right away that there's a problem.

Summary

I'm writing all this up because I know I'll run into this again next time I install a dev machine or even a new server and hopefully by then I'll remember that I wrote this blog post 😃. Maybe it'll help you too should you run into slow initial SQL connections as well.

this post created and published with Markdown Monster
Posted in Sql Server  

The Voices of Reason


 

Rik Hemsley
October 27, 2018

# re: Slow Connections with Sql Server


Scott Ferguson
October 28, 2018

# re: Slow Connections with Sql Server

From my understanding, if anything, Named Pipes should be quicker than TCP/IP. I wonder if the fact that you're not seeing that is more of a reflection of your network?

Anyway, according to the MS docs: "Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options."

Check it out for yourself here: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/choosing-a-network-protocol?view=sql-server-2014


Rick Strahl
October 29, 2018

# re: Slow Connections with Sql Server

@Scott - Yeah that's what I thought - but I tried on a few different machines including a VM that's not part of any Windows network. Each one of them is slow. And you would think named pipes would first look for local connections before jumping to another machine given that it's an explicit server specification. I use named pipes via code in some local applications on my dev machines for inter-process communication and that's not slow - connections/responses with that are instant.


JustMe
November 10, 2018

# re: Slow Connections with Sql Server

In the connection string, do not use the . [dot] or local for 'server'. Use localhost or an FQDN instead. While the . [dot] or local are valid (and still documented), they no longer work well with newer Windows/SQL/Powershell versions. Not sure if this fixes slow connections, but it resolves quite a few other issues including failed connections. Unfortunately, Microsoft is unwilling to document this new behavior.


Rick Strahl
November 10, 2018

# re: Slow Connections with Sql Server

I've never seen problems with . for local servers either with SQL (which definitely works) or with server designations from a command prompt or powershell.


Peter
December 01, 2020

# re: Slow Connections with Sql Server

thanks a lot for the input, i had 6 seconds for the connection and with the activation of TCP/IP i'm down to 0.01


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024