9

TL;DR: When connecting to my SQL Server Docker container via a name that resolves to the IPv6 loopback (::1), SMO calls are really slow. When using 127.0.0.1, they're fast.


I am trying to learn how to use the Docker image microsoft/mssql-server-windows-developer. Per Microsoft’s documentation, this container only exposes port 1433 TCP.

docker run -d -p 1433:1433 -e sa_password=Passw0rd! -e ACCEPT_EULA=Y -v C:\dockerdb:C:\dockerdb microsoft/mssql-server-windows-developer

I am running the container on Windows 10, and have been successful starting it, authenticating with SQL Server authentication, and running queries against the instance using sqlcmd and SSMS 17.4 on the windows host (connecting to localhost or “.”), and SQL Operations Studio on a mac next door connecting by IP. I see no noticeable performance issues when running queries this way.

In SSMS, I can also browse object explorer, but if I try to do something from the right click menu on an object in the object explorer, like open the instance parameters window or attach a database, SSMS does not show a response for about 5-10 minutes, at which point it either displays the window I asked for, or displays this error message:

SSMS Error message

I am also trying to do some PowerShell scripting against this instance using the SMO Scripter object, and see the same kind of behavior. The PS script loops through objects in the database and scripts them to file, and while it works to gather the list of objects relatively quickly, each individual object takes 5-10 minutes to script—too slow to be usable.

I have a hunch that the single exposed port is not enough and that SMO and SSMS are trying to connect in a similar way that is slowing them down. Could it also be that when connecting to localhost, these tools assume there to be other communication channels present that would typically not be firewalled? Are there any additional connection parameters I could be using? Can anyone validate my assumption that SSMS is using SMO or something else to talk to SQL Server?


UPDATE: I'm still investigating, but it's plausible that this is a Docker issue around resource constraints. This is confusing because most of the documentation seems to indicate that Windows Containers don't have any default resource constraints (and these can not be set in the Docker for Windows GUI — only for Linux containers), but it seems that in reality, Windows containers running on Windows 10 get a default RAM allocation of 1GB. I'm still trying to figure out how to inspect a running container to see its RAM and CPU allocation, but next I've got to just try increasing those from whatever the defaults are, using docker run parameters.


FURTHER UPDATE: I've been failing to get any kind of reliable metric out of docker that tells me what CPU and memory limits it has in place for the container. Varying research indicates that either docker containers don't have a memory limit by default, or that they do and it's 1GB, but all I can verify at the moment is that docker stats says the SQL container is only using between 750 and 850 meg, and when I try to add a run parameter to set available memory to 4 gb, it errors out. So I stopped following that thread of inquiry and went for a different gut check: entering an interactive powershell session on the running container and then invoking my powershell script linked above from inside the container.

Running inside the container there was no issue. It blazed through 2780 objects in just a couple of minutes. I think this confirms that the problem is with the container/host boundary, so I'm going to see if I can open up that UDP port. UPDATE: Opening port 1434 UDP didn't help.


MORE UPDATES—Workaround Achieved, Not a resource constraint issue: There appear to be issues related to setting large memory allocations for windows containers — I was receiving similar errors for 3g and 2g, but eventually was able to start the container with 1.5g, and I DID see a difference in the docker stats for the container that (I think) confirms it was running with a default allocation of 1GB. On default settings, the PRIV WORKING SET stat (which I can't find any documentation for, but my best guess is it's RAM) is between 700MiB and 850MiB. With docker run —memory="1.5g" set, it's around 1.0GiB. So it did expand, but appears to be leaving more of the allocation free than it did before. I interpret this (perhaps incorrectly) to mean that this server (which is running absolutely NO load and has NO user databases) is not under memory pressure. I checked the max server memory setting to confirm that it is set at the default maximum of 2PiB.

Then things got weird. I'm still testing things by running my powershell script from various locations. Fast inside the container, slow on the host. Then I RDP'd to another windows machine on the network, and ran the script from THAT machine, connecting to my Windows 10 host by IP. And it was FAST! This seems to support the theory that when connecting to something that's supposed to be localhost, SMO is trying to connect to SQL Server using something other than port 1433 TCP, that waits for a very long timeout before falling back to the TCP connection.

I decided to try validating this theory by entering a hosts file entry to refer to localhost by a name other than localhost:

        127.0.0.1       dockersucks

I connected in SSMS to dockersucks instead of localhost or ".", and immediately things were faster. Navigating object explorer was like usual, and opening panels like attach database or server properties happened as quickly as normal. And, when I ran my powershell script from the windows 10 host using this alias as the server name, it was fast as well.

I added this update to the question instead of an answer since I'm still looking for an explanation of why this is occurring, and if there's a way to fix it for connections to "localhost" by that name.

  • Maybe your docker instance is just under powered? If it was a port issue I doubt you'd be seeing it work at all, so I wouldn't spend much time down that road. – LowlyDBA Feb 9 '18 at 15:04
  • Yeah I was thinking about that, but query performance seems fine so it seems limited to only certain types of actions. I also need to test running the SMO stuff from INSIDE the container and verify that the issue is not present there. – NReilingh Feb 9 '18 at 15:22
  • 1
    I did not miss that part. SQL Server uses connection pooling. I get that this is frustrating, but I also want to impress upon you to ensure that the container (or "weird Hyper-V lite VM" in this case) has enough RAM. Your PS script ran "quickly", but minutes to run through ~3000 objects is slow in a machine with enough RAM (i.e. more than 2 GB). – Randolph West Feb 9 '18 at 18:56
  • 1
    To be perfectly honest, you're probably better off spinning up an Ubuntu Hyper-V VM on your machine, and installing SQL Server for Linux on that. – Randolph West Feb 9 '18 at 18:57
  • 1
    @bazzilic I explain why in my answer. Please comment there if you need clarification. – NReilingh May 30 '18 at 11:39
3

This is most likely a RAM starvation issue.

Things to check:

  • Does the container have 4 GB of RAM assigned to it? Check this answer.
  • Have you configured the Max Server Memory setting for SQL Server inside the container? Depending on how much RAM SQL Server can see in the container, this might be set to anything from 1 GB to 3.25 GB.
  • Is your host's RAM used up, and is it possible that Docker is paging to disk? Close any extraneous applications (web browsers are big consumers of RAM). SSMS needs around 1 GB of working RAM to be useable.
  • Is this faster after a reboot?

If I were doing this myself, I'd install the Docker Community Edition for Windows from the Docker store, and then install the SQL Server Docker image that way.

If your Internet connection is fast enough, you can be up and running in under 5 minutes, and be able to allocate resources in a much easier way.

EDIT: Ah, networking.

  • Not sure if you're misunderstanding this, but I'm not running SSMS inside the container. That's not possible, as Windows containers don't currently support RDP connections or GUIs. I already know SQL Server isn't slow — but I do need to figure out if I'm starving this docker container for resources. The Windows 10 host running the container and SSMS has 10GB of RAM and 2 cores, but I'm not sure how much is getting allocated to the container. – NReilingh Feb 9 '18 at 17:21
  • My answer has been rewritten – Randolph West Feb 9 '18 at 17:35
  • See Q update for additional information. Note that this is microsoft's own container image build, so I think we're pretty safe assuming that settings within the container are not going to be an issue. – NReilingh Feb 9 '18 at 18:39
  • 2
    Please don't make that assumption! – Randolph West Feb 9 '18 at 18:40
  • @NReilingh I have added a URL to my answer for you to investigate the -m option. – Randolph West Feb 9 '18 at 18:45
3

The key difference here is whether SSMS/SMO is attempting to connect with IPv4 or IPv6. If you do a ping localhost in a command prompt, you should see it resolve to ::1, which is the IPv6 equivalent of 127.0.0.1. Connecting to . does the same thing.

Your docker run command only exposes port 1433 on 127.0.0.1. You can verify this by running netstat -a to see which ports are available.

The hosts file alias you created resolves directly to 127.0.0.1, but you don't need it, since you can connect to 127.0.0.1 directly in SSMS and solve your problem that way. Turning off IPv6 entirely on your host system would probably also work, but I'm not sure how advisable this is on Windows 10.

I'll consider an alternate answer to accept if anyone can tell me why IPv6 is causing this to break.

  • Have you tried connecting to tcp:localhost ? It may be that SSMS/SMO are attempting a shared memory or named pipes connection when the name is localhost or (local) or even "." and ::1. I don't know if SSMS 17.4 definitely uses SMO in Object Explorer, but I think it is possible. – Mister Magoo Feb 10 '18 at 1:39
  • @MisterMagoo Entering tcp:localhost in the server name field doesn't appear to work at all, but I had tried explicitly specifying TCP/IP in connection properties without improvement. I still think the problem is slow fallback to 127.0.0.1 for localhost when ::1 fails. I think my query windows were working because they were maintaining a connection, whereas my script using SMO was (perhaps) creating new connections over and over again. – NReilingh Feb 12 '18 at 21:34
  • 1
    @NReilingh I'm seeing this same issue - your troubleshooting and explanation were really helpful. Addressing my sql server container as 127.0.0.1 (not localhost) from my host environment was the only way I could get host to container connections working normally. – rogersillito Jul 9 '18 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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