SQL Server 2019 for WIndows 11 Vs. SQL Server 2019 for Linux ...who is the fastest? the battle!

 Hi Guys,

Welcome to this post. 

In the previous posts here and here we have installed Windows 11 and SQL Server 2019 on a virtual machine and Ununtu (Linux) and SQL Server 2019 on another one.

This was done to run benchmarks to verify which system is the fastest.

Both VM has the same numerber of virtual processors (2), both has the same quantity of RAM (4gb) and both are installed on the same disk, a consumer samsung 1Tb SSD 860 qvo.

From the management studio located in my pc we will connect in turn to the VMs with installed windows 11 and the VM with installed Ubuntu 20.04 lts.

Enjoy the reading!

How to connect to the VMs

Connecting to the two VMs is simple!

For the VM with windows, with the SQL server developer and express edition you need to enable the TCP/IP protocol:

And open the TCP 1433 port in the firewall:

To get the IP value simply open the command line and type the IPCONFIG command.

For ubuntu VM machine, we need only to get the IP value by opening the terminal and then executing the command:

ip a            

Press enter

Out IP is 192.168.153.130

Now in both cases put the IP obtained in the connect login window of the object explorer:

 

Benchmark! ...and the winner is?

What we will do in particular today? We will analyze the three basic operation: INSERT, UPDATE and DELETE. 

Execution plan will be trivial.Doing so the difference in the results should be due to the filesystem of the operative system. Both VMs are placed on the same SSD disk.

At the beginning of each test the virtual machine is restarted to clear each cache.

 

Insert benchmark

We will create a simple heap table MOV without any index.

CREATE TABLE MOV (id int)            

Then we will run the script below to insert 100K rows and we will measure the time.

-- Insert 100K ROWS

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 100000  -- 100K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1)')
  SET @i = @i + 1
END;
       
DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 50000  -- 50K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2)')
  SET @i = @i + 1
END;

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 20000  -- 10K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2),(3),(4),(5)')
  SET @i = @i + 1
END;

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 10000  -- 10K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)')
  SET @i = @i + 1
END;

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 1000  -- 1K
BEGIN
  EXEC('INSERT INTO MOV (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                                    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
			      (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)')
  SET @i = @i + 1
END;
 

These are the results:

Then we will run the same script inserting 1 million of rows.

Update benchmark

This time we will create first an heap table with 2 fields:

CREATE TABLE MOV (id int, updval int );            

With the script below, we will measure the time to do 10.000 updates

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 10000  -- 10K
BEGIN
  EXEC('UPDATE MOV set updval = 1 where id = '+@i);
  SET @i = @i + 1
END;          

Then we will add a clustered index con the ID field and with the script below we will measure the time to do 100.000 updates:

DECLARE @i INTEGER;
SET @i = 0;
WHILE @i < 100000  -- 100K
BEGIN
  EXEC('UPDATE MOV set updval = 1 where id = '+@i);
  SET @i = @i + 1
END;          

These are the results:

Delete benchmark

This benchmark use the same logic of the update benchmark.

We will measure the time to delete 10.000 rows, then added a clustered index on the ID field we will measure the time to delete 100.000 rows.

These are the results:

 

Summary

In these benchmarks Windows 11 has been always more faster than Ubuntu 20.04 lts.

SQL Server 2019 on Linux was 10 to 30% slower than the windows version.

In the next posts we will compare them from the point of view of concurrent execution of queries. Who will scale best? Who will introduce fewer locks?

That's all for today,

Luca

Next post:

Previous post: SQL Server, install Windows 11 and SQL Server 2019 step by step