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