Extremely unpreditable performance and slow performance

[Nefcanto]

Hello guys.

I use this docker-compose.yml to setup a SQL Server docker container on Debian GNU/Linux 10 (buster):

version: "3.9"
services:
    database:
        image: mcr.microsoft.com/mssql/server:latest
        user: root
        container_name: OrganizationDatabases
        volumes:
            - /Organization/Databases:/var/opt/mssql/data
        environment:
            - ACCEPT_EULA=Y
            - SA_PASSWORD=password
            - MSSQL_PID=Express
        ports:
            - 1433:1433
        logging:
            driver: none
        networks:
            - OrganizationNetwork
        restart: always
networks:
    OrganizationNetwork:
        name: OrganizationNetwork
        driver: bridge

And I create two simple databases in it, each having a simple table, and I create one view.

create database Customers
go

use Customers
go

create table Customers
(
    Id bigint not null primary key identity(1, 1),
    [Name] nvarchar(100)
)
go

create database Sales
go

use Sales
go

create table Orders
(
    Id bigint not null primary key identity(1, 1),
    CustomerId bigint,
    Total int
)
go

create view OrderViews
as 

select
    s.*,
    c.[Name] as CustomerName
from Sales.dbo.Orders s
inner join Customers.dbo.Customers c
on s.CustomerId = c.Id
go

And I use docker exec -it OrganizationDatabase bash to get bash to the container. Then I use /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P password -d Sales to get a command interface. Then I run this query:

declare @start datetime
declare @end datetime
set @start = getdate()
set statistics time on

select *
from Orders

set statistics time off
set @end = getdate()
print cast(datediff(millisecond, @start, @end) as varchar(100)) + ' milliseconds'

And here's the response I get:

 SQL Server Execution Times:
   CPU time = 890 ms,  elapsed time = 2209 ms.
2206 milliseconds

Please note that there is no data in the databases.

The elapsed time is varying from 0 to sometimes even 2000 or even 3000 milliseconds.

In other issues, you have redirected users to the SQL Server best practices for Linux document. I tried to read it but I could not understand it. To be honest, it's very technical and as a developer, I'm against it.

We should not be OS gurus to get optimal performance out of the box. With all respect, that's your job not ours. It's too technical to be understood by even senior developers.

I use MariaDB container, and it works extremely well out of the box. I use MongoDB container and it also works extremely well out of the box.

One would expect SQL Server container to also work extremely well out of the box.

What can I do to fix this unpredictability? Please don't refer me to that technical document.

[Jayuda]

I compare SQL Server 2017 on windows Vs On Docker also very different performances. Docker mssql very slow

[tracker1]

What is your host environment? (MacOS, Windows, WSL, Linux) What are your system specifications, and how much Ram do you have associated with your docker environment?

There are a LOT of factors that can affect these things. SQL Linux/Docker doesn't seem to be optimized for lower memory environments in particular. IIRC, you want at least 4GB ram associated with the docker environment that MS-SQL Server runs in, not including additional overhead for the environment or other containers.

If you aren't using any of the more advanced features of SQL, you may also consider Azure SQL Edge, which tends to be much lighter weight (1gb ram, instead of 4).

[leblancmeneses]

I'm running mcr.microsoft.com/mssql/server:2019-latest on k8s on aws and for the most part it works satisfactory out of the box once I add this to the connection string.

ConnectRetryCount=5;ConnectRetryInterval=10;Connection Timeout=15;packet size=4096

management studio is slow to connect to k8 instance but once it connects for the most part it works. The connection does get randomly dropped so sql profiler is limited.

Problem was still reproducible after switching to c5.2xlarge from c5.xlarge so we stuck with c5.xlarge for now. https://stackoverflow.com/questions/73859640/mssql-server2019-latest-on-k8s-shows-high-other-system-cpu-utilization

Upgrading to 2022-latest dropped the complaints from being a P1 to P3 issue when using the instance as a development server.