Performance Testing Azure SQL Edge on Intel and M1 MacBooks

By: Aaron Bertrand   |   Updated: 2022-02-02   |   Comments (2)   |   Related: > Containers

Problem

I recently wrote about getting Docker and Azure SQL Edge up and running on the new MacBook Pro M1, and promised I would follow up with performance testing and comparisons. More specifically, I wanted to see if, with the same docker configuration on different platforms, the platform itself is giving you exaggerated performance in either direction.

Solution

To start, we need to set up Docker. As I mentioned last time, the download page for Docker Desktop offers packages for both Intel and M1, so choose appropriately:

Once Docker is up and running, I wanted to change the memory configuration, because the defaults are low (2 GB). Anthony Nocentino reminded me that SQL Server inside Docker will only see ~80% of the allocated memory so, by default, you'll see this in the errorlog when a SQL Server container starts up, regardless of how much memory the host machine has available:

Detected 1591 MB of RAM.

A gig and a half of memory is probably not the best configuration if you're testing anything of any substance at all, unless you really want to test your disks (I am much more interested in CPU, especially in this case). You can easily change this by going to Docker preferences, Resources, Advanced, and increasing Memory; this is useful for making sure that your containers have enough memory and also that multiple containers get along effectively:

I have 64GB on both machines, so I'll cap memory at 16.00 GB.

(Note that this will require a Docker restart, so be aware if you have other containers that you expect to keep running.)

Because I can only run Azure SQL Edge on my M1 MacBook, I’m going to pull that image on both machines:

docker pull mcr.microsoft.com/azure-sql-edge:latest

Now I can run a container with that image, running SQL Server on port 5001, and specifying 4 CPUs and 16 GB of memory:

docker run -d 
  --name SQLEdge 
  -e ACCEPT_EULA=Y 
  -e MSSQL_SA_PASSWORD=3dg3Y0urB3ts 
  -e MSSQL_PID=Developer 
  -p 5001:1433 
  --memory 16384m 
  --cpus 4 
  mcr.microsoft.com/azure-sql-edge:latest

If you check the logs, you’ll see that SQL Server is able to use just under 13 GB:

docker logs SQLEdge

Buried in the verbose output you should find something like this:

…
Detected 12810 MB of RAM.
…

Next, we can download AdventureWorksLT2019.bak from AdventureWorks sample databases, and copy it into the container:

docker exec -it SQLEdge mkdir "/var/opt/mssql/backup"
docker cp ~/Downloads/AdventureWorksLT2019.bak SQLEdge:/var/opt/mssql/backup/

Success looks something like this, including the output that shows the randomly generated container ID:

Next, we can open Azure Data Studio, and test our connections. On both machines, the initial connection looks like this (though the computer name will vary; it will be the first 12 characters of the container ID):

Then we can open a new query and confirm SQL Server is configured correctly:

SELECT memory_mb = total_physical_memory_kb/1024.0 FROM sys.dm_os_sys_memory;
SELECT cpu_count FROM sys.dm_os_sys_info;
SELECT @@VERSION;

Results on the Intel MacBook:

Results on the M1 MacBook:

Then we can restore AdventureWorksLT2019 with the following command (and we’ll turn Query Store on for both machines, just in case we want to look at those metrics later):

RESTORE DATABASE AdventureWorksLT2019  
  FROM DISK = '/var/opt/mssql/backup/AdventureWorksLT2019.bak'  
  WITH REPLACE, RECOVERY,  
  MOVE 'AdventureWorksLT2012_Data' TO '/var/opt/mssql/data/aw2019.mdf',  
  MOVE 'AdventureWorksLT2012_Log'  TO '/var/opt/mssql/data/aw2019.ldf'; 
ALTER DATABASE AdventureWorksLT2019 SET QUERY_STORE = ON;

To reiterate, I wanted to compare CPU performance of a similar workload between the M1 and Intel Macs, with as many things being equal as possible. The two machines are configured as identically as possible (though the M1 has 10 cores instead of 8, and the memory is DDR5):

All I needed was a CPU-heavy workload and something to drive it. I thought the best way to test this would be using SqlQueryStress, a long-standing and flexible workload generation tool actively maintained by Erik Ejlskov Jensen. However, it’s a Windows application – even the command line components have a WinForms dependency. While I do have Windows VMs and physical machines around, I didn't want to introduce network latency as an additional variable. I reached out to Erik, who very quickly churned out a cleaner command line version called sqlstresscmd.

To get this running, I had to install the latest .NET 6.0 SDK package from here (for the M1 I chose the SDK Installer for Arm64, and for Intel I chose the SDK Installer for x64). Then I made sure none of the dotnet libraries would cause a trust prompt (following advice from Maarten Merken):

cd /usr/local/share/dotnet  
find . -type f -print0 | xargs -0 xattr -d com.apple.quarantine

Then I added a symbolic link for dotnet in my bin folder, so I could always call it without worrying about where it lives:

cd /usr/local/bin  
ln -s /usr/local/share/dotnet/dotnet dotnet

I’ll need to call dotnet to install the cross-platform version of sqlstresscmd:

dotnet tool install -g sqlstresscmd

This put sqlstresscmd in /Users/aaronbertrand/.dotnet/tools/, so I made another symbolic link:

ln -s /Users/aaronbertrand/.dotnet/tools/sqlstresscmd sqlstresscmd

Then I downloaded the sample settings file and made some changes locally (only showing the lines I changed):

13     "Database": "AdventureWorksLT2019",
15     "IntegratedAuth": false,
16     "Login": "sa",
18     "Password": "3dg3Y0urB3ts",
19     "Server": "127.0.0.1,5001"
21     "MainQuery": "SELECT name FROM sys.tables;",
22     "NumIterations": 10,
23     "NumThreads": 1,

I put this file into ~/Documents/, so I made one more symbolic link:

cd /usr/local/bin  
ln -s ~/Documents docs

Now with these symbolic links in place, I could simply say:

sqlstresscmd -s docs/sample.json

Proof that it works on both machines (this is not a performance test):

I mentioned to Erik that I’d love to see slightly prettier output, and he delivered immediately. You can update to the newest version as simply as you installed (you may need sudo):

dotnet tool update -g sqlstresscmd

The result of the update command:

And now the output is both less verbose and more readable:

Now, let’s make these CPUs earn their caviar and champagne prices, shall we? I created this completely ridiculous stored procedure that does most of its work in a single sort operation, but should have plenty of additional impact on CPU:

CREATE OR ALTER PROCEDURE dbo.MakeSomeNoise
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @g bigint; 
  ;WITH x AS  
  ( 
    SELECT TOP (1+ABS(CHECKSUM(NEWID())) % 512) v.ProductID, 
      LongerString = CONVERT(varbinary(256), 
      CONCAT_WS('|', v.Name, v.Productmodel, v.Description, c.name)) 
    FROM SalesLT.vProductAndDescription AS v 
    CROSS APPLY 
    (
        SELECT TOP (1+ABS(CHECKSUM(NEWID())) % 128) STRING_AGG(name, '|')
          WITHIN GROUP (ORDER BY CHECKSUM(REVERSE(NEWID()))) 
        FROM sys.all_columns 
        GROUP BY object_id
        ORDER BY ASCII(LEFT(REVERSE(NEWID()),1))
      ) AS c(name) ORDER BY CHECKSUM(REVERSE(NEWID())) DESC
  ) 
  SELECT @g = ABS(1+ABS(CHECKSUM(NEWID())) % 256 
            - AVG(DATALENGTH(LongerString)) 
            + SUM(ProductID % ABS(CHECKSUM(NEWID())) / 128)) 
  FROM x
  ORDER BY CHECKSUM(REVERSE(NEWID()))
  OPTION (RECOMPILE, QUERYTRACEON 8649, MAX_GRANT_PERCENT = 10);
END
GO

I created a new .json file called MakeSomeNoise.json, with the following content:

{
  "CollectIoStats": true,
  "CollectTimeStats": true,
  "CommandTimeout": 0,
  "ConnectionTimeout": 15,
  "DelayBetweenQueries": 10,
  "EnableConnectionPooling": true,
  "ForceDataRetrieval": false,
  "KillQueriesOnCancel": true,
  "MainDbConnectionInfo": {
    "ApplicationIntent": 0,
    "ConnectTimeout": 15,
    "Database": "AdventureWorksLT2019",
    "EnablePooling": true,
    "IntegratedAuth": false,
    "Login": "sa",
    "MaxPoolSize": 2,
    "Password": "3dg3Y0urB3ts",
    "Server": "127.0.0.1,5001"
  },
  "MainQuery": "EXEC dbo.MakeSomeNoise;",
  "NumIterations": 100,
  "NumThreads": 1,
  "ParamDbConnectionInfo": {
    "ApplicationIntent": 0,
    "ConnectTimeout": 0,
    "Database": "AdventureWorksLT2019",
    "EnablePooling": true,
    "IntegratedAuth": false,
    "Login": "sa",
    "MaxPoolSize": 2,
    "Password": "3dg3Y0urB3ts",
    "Server": "127.0.0.1,5001"
  },
  "ParamMappings": [
  ],
  "ParamQuery": "",
  "ShareDbSettings": true
}

Then I called sqlstresscmd on both machines with 4, 8, 16, and 32 threads:

sqlstresscmd -s docs/MakeSomeNoise.json -t 4
sqlstresscmd -s docs/MakeSomeNoise.json -t 8
sqlstresscmd -s docs/MakeSomeNoise.json -t 16
sqlstresscmd -s docs/MakeSomeNoise.json -t 32

These screen shots aren’t doctored, per se, but I did use Photoshop to take away small details like the Start time (hopefully I cobbled it back together convincingly):

A much clearer visualization just plots duration:

While I tried to make everything as equal as possible, the M1 machine consistently ran slightly longer (the first test of any kind where the newer machine didn’t come out on top). Perhaps that is because somewhere in this specific stack there is something that hasn’t been optimized for Apple silicon. Or maybe it is just that the M1 doesn’t let a process run the machine into the ground – one subjectively better thing is that the fan didn’t kick in at all, but on the Intel Mac, they were screaming throughout these tests. I’m not exaggerating – the M1 was completely silent, while I was afraid to touch the Intel to anger it further.

Conclusion

I would love to understand this discrepancy better, but I should probably start by making a more realistic, mixed workload, and try it from different tools or scripts, maybe remotely, which introduces network but removes potentially conflicting local resources. I don’t like the feeling in the pit of my stomach that this much-heralded-as-superior machine is inferior at one of the things I’d like to use it for, but I must remember all the other things it’s great at (including, quite noticeably in this case, being quiet). In the meantime, having a local and familiar stress-testing application is still a great tool to have in your toolbelt, regardless of what type of computer you use or whether its results don’t line up precisely to other machines. And I have plenty of other tests planned before I feel too gloomy about the M1; this was just to lay the groundwork.

Next Steps

Go grab sqlstresscmd to make ad hoc workload generation easier regardless of your platform.

See these tips and other resources:

• Testing SQL Server Edge and Docker on the latest MacBooks
• SqlQueryStress on GitHub
• Download page for Docker Desktop
• Run SQL Server vNext (CTP1) as a Docker Container on a Mac
• Build Docker Containers with External Storage on Your Desktop
• SQL Server 2017 installation on Ubuntu using Docker
• Edwin Sarmiento’s series on Docker containers: Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6
• Run and Manage SQL Server 2019 CTP 2.0 RHEL Docker Container
• All SQL Server on Linux tips

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips