Performance difference vs Windows Instance

[chrismcv]

Hi,
I've been migrating my Windows instance over to containerd on WSL2. I'm finding there is quite a performance difference between the two.

I've a dotnet core executable which provisions a database and runs scripts (about 4MB worth) on it. On windows it takes 28s to run, against the docker image it takes 1m5s. I've tried running the client from both WSL (1m5s) and from Windows(1m4.9s), and see similar issues.

My command to run up the instance is:

	nerdctl run -d --name sql -e 'MSSQL_PID: Developer' -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=apassword' -p 2198:1433 -v .sql-data/data:/var/opt/mssql/data mcr.microsoft.com/mssql/server:latest

Can anyone shed any light on how to diagnose this?

Thanks,
Chris

[amitkh-msft]

Hi, are the resources given to both WSL and Windows instance the same? Overall the SQL Server engine is the same when you run on Windows or Linux. Also, what is the storage associated with the database when you are using the WSL2, can you try SQL Server on dedicated Linux VM or container and then see if the same issue exists. SQL on WSL 2 is currently not supported for production or equivalent workload.

[chrismcv]

Hi @amitkh-msft, yes, this is running on the same machine, and there is plenty of headroom in capacity for resource not to be the issue. Storage wise, both are running on the same NVMe SSD. For WSL2, its using the default file system and a folder in my home directory, so would be on the virtualised ext4 filesystem. I can't really run it on a like for like dedicated VM, and a different VM might reduce clarity further? I'm using this for my dev environment, so lack of production support isn't an issue as such.

[filipdot]

I was on docker using 2017 version and it was ok (not as good but usable), migrate to 2022 and at start it was faster, but after 2 weeks it became dog slow.
Rebuild from backup after wipe and same behavior.
compose:
`version: "3.8"

services:
mssql:
container_name: mssql
hostname: mssql
image: mcr.microsoft.com/mssql/server:2022-latest
stdin_open: true # docker run -i
tty: true # docker run -t
restart: always
environment:
- MSSQL_SA_PASSWORD=12345Password!
- SA_PASSWORD=
- ACCEPT_EULA=Y
- MSSQL_PID=Express
- MSSQL_COLLATION=Polish_CI_AS
- MSSQL_MEMORY_LIMIT_MB=8192
- MSSQL_BACKUP_DIR=/var/opt/mssql/backup
- TZ=Europe/Warsaw
ports:
- "1434:1433"
volumes:
- mssql_data:/var/opt/mssql
- backup:/var/opt/mssql/backup
- /etc/timezone:/etc/timezone:ro
- /etc/localtime:/etc/localtime:ro
volumes:
mssql_data:
name: mssql_data # This line is not allowed to be changed
backup:
name: backup
driver_opts:
type: cifs
o: "username=mssql-backup,password=XXX,vers=3.0,file_mode=0777,dir_mode=0777,rw,uid=10001,gid=10001"
device: "//XXX.XXX.XXX.XXX/mssql"`

I migrate again to Windows Server 2022 and SQL 2022 and it's the fastes.

All is on Proxmox using as VM on same SSD's, same CPU.

• ubuntu 22.04 LTS and docker
• Windows Server 2022.

Some forum threads:

• https://forum.proxmox.com/threads/mssql-on-zfs.56992/

Same data base, same app, same users and on docker it's became painful slow.

[amitkh-msft]

could you share sample queries for the purpose of context to understand the slowness that you describe? Additionally, could you please open a support ticket for our team to investigate this further?

[amitkh-msft]

could you share sample queries for the purpose of context to understand the slowness that you describe? Additionally, could you please open a support ticket for our team to investigate this further?

[filipdot]

i'm using ERP software INSert ERP from insert.com.pl and it's not depend on any specific query - but general reading, writing, updating etc. i see performance scale across the board. i test it on internal accounting to test some "real life" workload but accounting couldn't do it's work - it was so slow.
It's not official supported on linux SQL, but it's SQL so it's should work. But idk why performance keeps slowing down on linux. only backup was faster than windows host but even that became dog slow.
If i can make any diagnostic let me know i will try to provided it.
When trying to find problem i only see some network I/O delay and waiting task go from 0 to 1 to 0 to 1 or stay on 1. but couldn't define why.
adding extra templog files didn't help
my only idea was because it's wasn't used heavy all the time, process was mark as background so when i use it heavy it have to low priority. after redeploy of container and restore DB from backup for some time it will be back to snappy. maybe it's about memory priority. idk really.
How to open a support ticket to investigate this further?