SQL Server very slow for Management Studio interactions

Docker Desktop for Windows
#1

I am running the microsoft/mssql-server-windows-developer container on Windows 10.

Here is my docker version:

Client:
Version: 17.09.0-ce
API version: 1.32
Go version: go1.8.3
Git commit: afdb6d4
Built: Tue Sep 26 22:40:09 2017
OS/Arch: windows/amd64

Server:
Version: 17.09.0-ce
API version: 1.32 (minimum version 1.24)
Go version: go1.8.3
Git commit: afdb6d4
Built: Tue Sep 26 22:50:27 2017
OS/Arch: windows/amd64
Experimental: true

I bring up my container using docker compose:

version: '2.2’
services:
db1:
image: microsoft/mssql-server-windows-developer
volumes:
- db-data:C:\sqldata
environment:
sa_password: "mypass"
ACCEPT_EULA: "Y"
hostname: ms_sql
volumes:
db-data:

I have a web app running in another container. When it contacts the sql server, the performance is acceptable.
However, when I connect from my host using SSMS, the performance is very slow. Simple operations like connecting, viewing the list of databases, viewing the list of tables, opening a query window, etc. each take 15-30 seconds.

I have tried modifying my compose file to increase resources available to sql server:

mem_limit: 4g
cpu_percent: 50
cpu_count: 2

But this did not make a noticeable difference. The Vmmem process consumes the full amount of memory available, all the time. The CPU hovers around 1% and does not appear to increase during a slow operation.

Ideas on why this is running so slowly?

#2

For some reason when I connect to SQL Server via hostname (‘ms_sql’ in your case) it works extremely slow.
Try to connect with IP address instead. This worked for me.

#3

Thank you, this worked for me!

This necessitates using port forwarding, as my docker container gets a new dynamic IP address every time it starts.

Forwarding port 1433 from the host to the container, I see some strange behavior. The loopback address does not forward the port, but using the IP for the host does work. So if I connect SQL from the host machine to localhost or 127.0.0.1 it times out. But if I connect to the ip address for the host, the port forwards correctly.

Ideas on how to do port forwarding while using localhost?

#4

Apparently the loopback issue is a known limitation:

For those following along at home, one way to get around this is to use docker inspect to get the container’s IP address and then add it to your host file, using Powershell.

#5

I assume that you are trying to set up DEV machine environment. The only thing I can suggest here is to create user-defined network with ‘nat’ driver. User defined network allows to set static IP address for containers. So you don’t have to change ‘hosts’ file each time.

At least that worked for me.