patternsqldockerMinor
SMO, SSMS are slow for management of SQL Server in Docker when connecting to localhost
Viewed 0 times
ssmsserveraresqldockerconnectinglocalhostslowsmofor
Problem
TL;DR: When connecting to my SQL Server Docker container via a name that resolves to the IPv6 loopback (
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.
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
::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-developerI 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
Solution
This is most likely a RAM starvation issue.
Things to check:
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.
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.
Context
StackExchange Database Administrators Q#197499, answer score: 3
Revisions (0)
No revisions yet.