Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux

The article consists of some benchmark tests of SQL Server on different Operating Systems, the parameters considered are CPU Time, Elapsed Time and Total Execution Time.

Introduction 

 
This article is inspired from “A study for Performance Comparison of Different In-Memory Databases” where the author compares the query response time of four in-memory databases TimesTen, Altibase, solidDb, and SQLite on Linux and Windows environments. This article uses SQL Server 2017 on Windows Server 2012 R2 and Ubuntu Linux environment. Here, I have covered the queries with different complexity levels and access data amounts to be executed. In this article, the steps are also mentioned with details on how to set up an environment for testing. I assume that the reader already knows a bit about SQL Server and Management Studio on Windows platform, that's why more focus is on Ubuntu Linux environment.
 

Test System Detail

 
The purpose of this study is to compare the performance of SQL Server in terms of CPU time, elapsed time and total execution time. To conduct this study, two similar machines in terms of hardware were setup. All tests were performed using the same set of queries on the same machines. The queries were using the same row or id, tables, and columns on both machines. The two machines are identical except the operating system. One has Windows Server 2012 R2 64 bit and other has Ubuntu Linux 16.04- Server 64 bit. Both machines have two CPU with Intel(R) Xeon(R) CPU E5-2673 v4 @ 2.30GHz processor. The RAM on both machines is 8GB. The hard disk used on both machines is 128GB SSD. The products used in testing are SQL Server 2017 Express Edition and SQL Operation Studio (preview) 0.24.1. SQL Operation Studio is a free lightweight tool that can be used for development or operations on Windows, Linux or macOS environment.
 
The database used in tests is ContosoBIdemo which has a large sample data with up to 12,627,608 rows.
 
The size of rows in each table of the database is like below.
 
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 

Tests, results, and observations

 
We already have SQL Server and SQL Operation Studio installed on machines, let's start SQL Operation Studio and run our test queries.
 
In Windows, we can start SQL Operation Studio from the application list and click it. The setting for the connection can be like below,
 
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
In Ubuntu Linux, the settings for the connection can look like below; 
  1. sqlops  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 01
 
Restore the database using TSQL. The size of the backup .bak file is 645MB and restored database is 1.66GB
 
Windows
  1. SET STATISTICS TIME On  
  2. ALTER DATABASE ContosoRetailDW  
  3. SET SINGLE_USER  
  4. WITH ROLLBACK IMMEDIATE;  
  5.   
  6. RESTORE DATABASE ContosoRetailDW  
  7. FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\ContosoRetailDW.bak'  
  8. WITH REPLACE,  
  9. MOVE 'ContosoRetailDW2.0'   
  10. TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\ContosoRetailDW.mdf',  
  11. MOVE 'ContosoRetailDW2.0_log'   
  12. TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\ContosoRetailDW.ldf';  
  13. ALTER DATABASE ContosoRetailDW  
  14. SET MULTI_USER;  
  15. SET STATISTICS TIME OFF  
Linux
  1. SET STATISTICS TIME On  
  2. alter database ContosoRetailDW  
  3. set single_user with ROLLBACK IMMEDIATE;  
  4.   
  5. restore database ContosoRetailDW  
  6. from disk ='/var/opt/mssql/ContosoRetailDW.bak'  
  7. with replace,  
  8. move 'ContosoRetailDW2.0'  
  9. to '/var/opt/mssql/data/ContosoRetailDW.mdf',  
  10. move 'ContosoRetailDW2.0_log'  
  11. to '/var/opt/mssql/data/ContosoRetailDW.ldf';  
  12.   
  13. alter database ContosoRetailDW set multi_user;  
  14. SET STATISTICS TIME OFF  
Query 02
 
Retrieve a record based on the primary key
  1. SET STATISTICS TIME On  
  2.   
  3. select top 1000 * from FactStrategyPlan  
  4. where StrategyPlanKey = 2181378  
  5. order by StrategyPlanKey desc  
  6.   
  7. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 03
 
Retrieve a record based on foreign key
  1. SET STATISTICS TIME On  
  2.   
  3. select count(*) from FactStrategyPlan  
  4.   
  5. where ProductCategoryKey = 5  
  6.   
  7. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 04
 
Retrieve a record when there is no key for any column
  1. SET STATISTICS TIME On  
  2.   
  3. select count(*) from FactStrategyPlan  
  4. where Amount > 3000 and Amount < 4500  
  5.   
  6. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 05
 
Retrieve the data when joining tables. Three tables are joined, FactSales has 3,406,089 rows, DimDate has 2556 rows and DimChannel has 4 rows.
  1. SET STATISTICS TIME On  
  2.   
  3. SELECT dd.CalendarMonth, dc.ChannelName, sum(fs.SalesAmount) TotalSaleAmount  
  4. FROM FactSales fs  
  5. JOIN DimDate dd ON dd.DateKey = fs.DateKey  
  6. JOIN DimChannel dc ON dc.ChannelKey = fs.ChannelKey  
  7. WHERE dd.CalendarYear = 2007  
  8. GROUP BY dd.CalendarYear, dd.CalendarMonth, dc.ChannelName  
  9. ORDER BY dc.ChannelName, dd.CalendarYear, dd.CalendarMonth  
  10.   
  11.   
  12. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 06
 
Retrieve data when joining a big table. The three tables are; FactOnlineSales with 12,627,608 rows, DimDate has 2556 rows and DimStore has 306 rows.
  1. SET STATISTICS TIME On  
  2.   
  3. SELECT dd.CalendarMonth, ds.StoreName, sum(fos.SalesAmount) TotalSaleAmount  
  4. FROM FactOnlineSales fos  
  5. JOIN DimDate dd ON dd.DateKey = fos.DateKey  
  6. JOIN DimStore ds ON ds.StoreKey = fos.StoreKey  
  7. WHERE dd.CalendarYear = 2007  
  8. GROUP BY dd.CalendarYear, dd.CalendarMonth, ds.StoreName  
  9. ORDER BY ds.StoreName, dd.CalendarYear, dd.CalendarMonth  
  10.   
  11.   
  12. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 07
 
Update a table "FactOnlineSales" with rows 12,627,608. We are altering tables with a new column with the default value of "newsequentialid()". It is a GUID but they are created in sequence so reading/fetching a record is faster.
  1. SET STATISTICS TIME On  
  2.   
  3. ALTER TABLE  FactOnlineSales  
  4.   ADD SeqGuid UNIQUEIDENTIFIER  
  5.   CONSTRAINT FOS_FactOnlineSales_SeqGuid DEFAULT (newsequentialid())   
  6. with values  
  7.   
  8. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 08
 
Update a table "FactOnlineSales" with rows 12,627,608. We are altering tables with a new column with the default value of "newid()". It is a GUID value.
  1. SET STATISTICS TIME On  
  2.   
  3. ALTER TABLE  FactOnlineSales  
  4.   ADD NewGuid UNIQUEIDENTIFIER  
  5.   CONSTRAINT FOS_FactOnlineSales_NewGuid DEFAULT (newid()) with values  
  6.   
  7. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 09
 
We are trying to retrieve one row from 12,627,608 rows using sequential GUID.
  1. -- Record id = 27063240  
  2. -- Windows  
  3. SET STATISTICS TIME On  
  4.   
  5.  select * from FactOnlineSales  
  6.  where seqguid = 'a53bb53e-f36b-1410-bba5-800000000000'  
  7.   
  8.   
  9. SET STATISTICS TIME Off  
  10.   
  11. --Linux  
  12.   
  13. SET STATISTICS TIME On  
  14.   
  15.  select * from FactOnlineSales  
  16.  where seqguid = '7541a8cb-17ec-e711-80c3-000d3a16ac77'  
  17.   
  18. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux
 
Query 10
 
We are trying to retrieve one row from 12,627,608 rows using non-sequential GUID.
  1. --Record id = 27063240  
  2.    
  3. --Windows  
  4. SET STATISTICS TIME On  
  5.   
  6.   select * from FactOnlineSales  
  7.  where newguid = 'f22ff329-d2f5-4637-a5dd-847d8b77786b'  
  8.   
  9. SET STATISTICS TIME Off  
  10.   
  11. --Linux   
  12.   
  13. SET STATISTICS TIME On  
  14.    
  15.  select * from FactOnlineSales  
  16.  where newguid = '2fea845d-3467-438b-8fae-49b8143c810e'  
  17.   
  18. SET STATISTICS TIME Off  
Comparison Of Different SQL Query Responses Of SQL Server On Windows And Ubuntu Linux

Conclusion

 
From the result figures, it looks that SQL Server on Linux works as well as Windows when reading data using Select while Windows environment has better performance for SQL Server when it comes to writing; for example, restore of database or update table.
 
To understand the result, it is important to understand some basics of how SQL Server actually works. The SQL Server is an in-memory database that is designed to store data in the memory to provide significant improvement in performance by fast query response time. The older approach was disk I/O operations which is very slow if compared to in-memory.
 
In our results, we have 3 parameters that were taken under consideration for testing. One is CPU time; it is the actual time that CPU on the machine takes to finish the job, it is our major point of interest. Elapsed time is CPU time plus wait time; the wait time includes time for reading from disk, locks, waiting for locks or latches and the wait time to fetch the row. The total execution time is the time that is required for parsing, compiling and executing each statement.
 
SQL Server 2017 comes with technologies like DirectIO and NUMA (Non-Uniform Memory Access). DirectIO uses Direct_IO parameter. For Linux environments, Direct IO is used while for Windows environment, SQL Server uses Informix I/O subsystem.
 
Direct I/O provides direct access to read and write for the disk by bypassing the file system buffers. The direct I/O can be configured using Direct I/O API and configuring the DIRECT_IO parameter. When Direct I/O is used, the performance for cooked files can match the performance of raw devices used for dbspace chunks. On Windows, Informix I/O subsystem is implemented, which is used as overlapped or asynchronous operations without OS buffering. So, DIRECT I/O is not required for the Windows environment.
 
NUMA (non-uniform memory access) helps memory at different points of a processor to have different performance. Microsoft Windows has developed a subsystem of NUMA that enable the system to place memory structure efficiently. Memory-access speed is required in enterprise-class applications rather than high computing systems, so less effort is needed on NUMA memory handling in Windows when compared with Linux.
 
From the above facts from reliable resources, it looks that NUMA works a bit better on Windows or provides the same level of performance when reading/retrieving operations are executed, while Informix I/O works better than DIRECT I/O.
 
These tests can be extended with more complicated TSQL to explore more performance-related facts.