Performance Testing Azure SQL Edge on Intel and M1 MacBooks

By:   |   Updated: 2022-02-02   |   Comments (2)   |   Related: More > 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:

Docker Desktop platform-specific downloads

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:

Changing memory resources in Docker Desktop

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:

Setting up docker from a command prompt

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):

test connections in Azure Data Studio

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:

Configuration details on the Intel MacBook

Results on the M1 MacBook:

Configuration details 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):

compare cpu performance

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):

Quick test confirming functionality on

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:

Output after updating sqlstresscmd

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

Updated tabular output for sqlstresscmd

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):

Comparison of Intel and M1 given a contrived workload with 4, 8, 16, and 32 threads.

A much clearer visualization just plots duration:

Workload Duration, in seconds

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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.

View all my tips


Article Last Updated: 2022-02-02

Comments For This Article




Friday, February 4, 2022 - 10:24:19 AM - Aaron Bertrand Back To Top (89747)
Hey Ali, IMHO CLR is a dead end too, since it is not supported in most Azure choices and that's where we're heading, I don't see it picking up there and honestly I don't find a lot that we can't do without CLR anyway. Split/agg strings is solved, format was a bad idea and is terrible for performance, and we have all these other language integrations now that make CLR not all that interesting anymore (IMHO).

I suspect they will eventually support at least a container image for the full version on ARM eventually, but I don't have any internal insight about those plans.

Thursday, February 3, 2022 - 10:17:35 PM - Ali Parvaresh Back To Top (89734)
Great article, this is what I was feeling too. On M1 it doesn't work as it was working on Intel.

BTW, do you know if Microsoft got any plans to support ARM64 in SQL Server 2022? There are features lacking in Azure Edge like CLR, etc...


download














get free sql tips
agree to terms