MS SQL Server on Linux vs Windows Performance Test to Spot the Difference

Since the release of SQL Server 2017 for Linux, Microsoft has pretty much changed the entire game. It enabled a whole new world of possibilities for their famous relational database, offering what was only available in the Windows space until then.

I know that a purist DBA would tell me right away that the out of the box SQL Server 2019 Linux version has several differences, in terms of features, in regards to its Windows counterpart, such as:

• No SQL Server Agent
• No FileStream
• No System Extended Stored Procedures (e.g. xp_cmdshell)

However, I got curious enough to think “what if they can be compared, at least to some extent, against things that both can do?” So, I pulled the trigger on a couple of VMs, prepared some simple tests, and collected data to present to you. Let’s see how things turn out!

Initial Considerations

Here are the specs of each VM:

• Windows
  • Windows 10 OS
  • 4 vCPUs
  • 4 GB RAM
  • 30 GB SSD

• Linux
  • Ubuntu Server 20.04 LTS
  • 4 vCPUs
  • 4 GB RAM
  • 30 GB SSD

For the SQL Server Version, I picked the very latest one for both Operating Systems: SQL Server 2019 Developer Edition CU10

In each deployment, the only thing enabled was Instant File Initialization (enabled by default on Linux, enabled manually on Windows). Other than that, the default values remained for the rest of the settings.

• In Windows, you can choose to enable Instant File Initialization with the installation wizard.

This post will not cover the specificity of the Instant File Initialization work in Linux. However, I’ll leave you a link to the dedicated article that you can read later (note that as it gets a bit heavy on the technical side).

What does the Test Include?

1. In each SQL Server 2019 instance, I deployed a test database and created one table with only one field (an NVARCHAR(MAX)).
2. Using a randomly generated string of 1,000,000 characters, I performed the following steps:
   • *Insert X number of rows into the test table.
   • Measure how much time it took to complete the INSERT statement.
   • Measure the size of the MDF and LDF files.
   • Delete all rows in the test table.
   • **Measure how much time it took to complete the DELETE statement.
   • Measure the size of the LDF file.
   • Drop the test database.
   • Create the test database again.
   • Repeat the same cycle.

*X was performed for 1,000, 5,000, 10,000, 25,000 and 50,000 rows.

**I know that a TRUNCATE statement does the job way more efficiently, but my point here is to prove how well each transaction log is managed for the delete operation in each OS.

You can proceed to the website I used to generate the random string if you want to dig deeper.

Here are the sections of the TSQL code I used for tests in each Operating System:

Linux TSQL Codes

Database and Table creation

DROP DATABASE IF EXISTS test
CREATE DATABASE test
    ON
(FILENAME= '/var/opt/mssql/data/test.mdf', NAME = test, FILEGROWTH = 128MB)
LOG ON
(FILENAME= '/var/opt/mssql/data/test_log.ldf',NAME = test_log, FILEGROWTH = 64MB);

CREATE TABLE test.dbo.ubuntu(
    long_string NVARCHAR(MAX) NOT NULL
)

Size of the MDF and LDF files for the test database

SELECT 
        DB_NAME(database_id) AS 'DB',
        type_desc AS 'Type',
        state_desc AS 'State',
        CONVERT(DECIMAL(10,2),size*8/1024) AS 'Size',
        CONVERT(DECIMAL(10,2),growth*8/1024) AS 'Growth'
FROM    sys.master_files
WHERE   DB_NAME(database_id) = 'test'

The below screenshot shows the data files’ sizes when nothing is stored in the database:

Queries to determine if Instant File Initialization is enabled

SELECT 
       servicename,
       instant_file_initialization_enabled
FROM   sys.dm_server_services
WHERE  servicename = 'SQL Server (MSSQLSERVER)'

Windows TSQL Codes

Database and Table creation

DROP DATABASE IF EXISTS test
CREATE DATABASE test
    ON
(FILENAME= 'S:\Program Files\Microsoft SQL Server\MSSQL15.WINDOWS\MSSQL\DATA\test.mdf', NAME = test, FILEGROWTH = 128MB)
LOG ON
(FILENAME= ''S:\Program Files\Microsoft SQL Server\MSSQL15.WINDOWS\MSSQL\DATA\test_log.ldf',NAME = test_log, FILEGROWTH = 64MB);

CREATE TABLE test.dbo.windows(
    long_string NVARCHAR(MAX) NOT NULL
)

Size of the MDF and LDF files for the test database

SELECT 
        DB_NAME(database_id) AS 'DB',
        type_desc AS 'Type',
        state_desc AS 'State',
        CONVERT(DECIMAL(10,2),size*8/1024) AS 'Size',
        CONVERT(DECIMAL(10,2),growth*8/1024) AS 'Growth'
FROM    sys.master_files
WHERE   DB_NAME(database_id) = 'test'

The following screenshot shows data files’ sizes when nothing is stored in the database:

Query to determine if Instant File Initialization is enabled

SELECT 
       servicename,
       instant_file_initialization_enabled
FROM   sys.dm_server_services
WHERE  servicename = 'SQL Server (MSSQLSERVER)'

Script to perform the INSERT statement:

@limit -> here I specified the number of rows to insert in the test table

For Linux, since I executed the script using SQLCMD, I put the DATEDIFF function at the very end. It lets me know how many seconds the whole execution takes (for the Windows variant, I could’ve simply taken a glimpse at the timer in SQL Server Management Studio).

The whole 1,000,000 characters’ string goes instead of ‘XXXX’. I’m putting it like that only to present it nicely in this post.

SET NOCOUNT ON
GO
DECLARE @StartTime DATETIME;
DECLARE @i INT;
DECLARE @limit INT;
SET @StartTime = GETDATE();
SET @i = 0;
SET @limit = 1000;

WHILE(@i < @limit)
BEGIN
	INSERT INTO test.dbo.ubuntu VALUES('XXXX');
	SET @i = @i + 1
END

SELECT DATEDIFF(SECOND,@StartTime,GETDATE()) AS 'Elapsed Seconds';

Script to perform the DELETE statement

SET NOCOUNT ON
GO
DECLARE @StartTime DATETIME;
SET @StartTime = GETDATE();

DELETE FROM test.dbo.ubuntu;

SELECT DATEDIFF(SECOND,@StartTime,GETDATE()) AS 'Elapsed Seconds';

The Results Obtained

All sizes are expressed in MB. All timing measurements are expressed in seconds.

Key Insights

• The size of the MDF was pretty much consistent across the entire test, varying slightly at the very end (but nothing too crazy).
• The timings for INSERTs were better in Linux for the most part, except the very end when Windows “won the round”.
• The size of the transaction log file was better handled in Linux after each round of INSERTs.
• The timings for DELETEs were better in Linux for the most part, except the very end where Windows “won the round” (I find it curious that Windows also won the final INSERT round).
• The size of the transaction log files after each round of DELETEs was pretty much a tie in terms of ups and downs between the two.
• I would’ve liked to test with 100,000 rows, but I was a bit short of disk space, so I capped it at 50,000.

Conclusion

Based on the results obtained from this test, I’d say there’s no strong reason to claim that the Linux variant performs exponentially better than its Windows counterpart. Of course, this is by no means a formal test that you can ground on to make such a decision. However, the exercise itself was interesting enough for me.

I would guess that SQL Server 2019 for Windows sometimes gets a bit behind (not by much) due to the GUI rendering in the background, which is not happening in the Ubuntu Server side of the fence.

If you rely heavily on features and capabilities that are exclusive to Windows (at least at the time of this writing), then by all means go for it. Otherwise, you will hardly make a bad choice by going for one over the other.