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

sqlops

SET STATISTICS TIME On;

ALTER DATABASE ContosoRetailDW
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE ContosoRetailDW
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\ContosoRetailDW.bak'
WITH REPLACE,
MOVE 'ContosoRetailDW2.0' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\ContosoRetailDW.mdf',
MOVE 'ContosoRetailDW2.0_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\ContosoRetailDW.ldf';

ALTER DATABASE ContosoRetailDW
SET MULTI_USER;

SET STATISTICS TIME OFF;

SET STATISTICS TIME On;

ALTER DATABASE ContosoRetailDW
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE ContosoRetailDW
FROM DISK = '/var/opt/mssql/ContosoRetailDW.bak'
WITH REPLACE,
MOVE 'ContosoRetailDW2.0' TO '/var/opt/mssql/data/ContosoRetailDW.mdf',
MOVE 'ContosoRetailDW2.0_log' TO '/var/opt/mssql/data/ContosoRetailDW.ldf';

ALTER DATABASE ContosoRetailDW
SET MULTI_USER;

SET STATISTICS TIME OFF;

SET STATISTICS TIME On;

SELECT TOP 1000 *
FROM FactStrategyPlan
WHERE StrategyPlanKey = 2181378
ORDER BY StrategyPlanKey DESC;

SET STATISTICS TIME Off;

SET STATISTICS TIME On;

SELECT COUNT(*)
FROM FactStrategyPlan
WHERE ProductCategoryKey = 5;

SET STATISTICS TIME Off;

SET STATISTICS TIME On;

SELECT COUNT(*)
FROM FactStrategyPlan
WHERE Amount > 3000 AND Amount < 4500;

SET STATISTICS TIME Off;

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.

SET STATISTICS TIME On;

SELECT dd.CalendarMonth, dc.ChannelName, SUM(fs.SalesAmount) AS TotalSaleAmount
FROM FactSales fs
JOIN DimDate dd ON dd.DateKey = fs.DateKey
JOIN DimChannel dc ON dc.ChannelKey = fs.ChannelKey
WHERE dd.CalendarYear = 2007
GROUP BY dd.CalendarYear, dd.CalendarMonth, dc.ChannelName
ORDER BY dc.ChannelName, dd.CalendarYear, dd.CalendarMonth;

SET STATISTICS TIME Off;

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.

SET STATISTICS TIME On;

SELECT
    dd.CalendarMonth,
    ds.StoreName,
    SUM(fos.SalesAmount) AS TotalSaleAmount
FROM
    FactOnlineSales fos
    JOIN DimDate dd ON dd.DateKey = fos.DateKey
    JOIN DimStore ds ON ds.StoreKey = fos.StoreKey
WHERE
    dd.CalendarYear = 2007
GROUP BY
    dd.CalendarYear,
    dd.CalendarMonth,
    ds.StoreName
ORDER BY
    ds.StoreName,
    dd.CalendarYear,
    dd.CalendarMonth;

SET STATISTICS TIME Off;

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.

SET STATISTICS TIME On;

ALTER TABLE FactOnlineSales
    ADD SeqGuid UNIQUEIDENTIFIER CONSTRAINT FOS_FactOnlineSales_SeqGuid DEFAULT (newsequentialid()) with values;

SET STATISTICS TIME Off;

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.

SET STATISTICS TIME On;

ALTER TABLE FactOnlineSales
    ADD NewGuid UNIQUEIDENTIFIER CONSTRAINT FOS_FactOnlineSales_NewGuid DEFAULT (newid()) with values;

SET STATISTICS TIME Off;

Query 09. We are trying to retrieve one row from 12,627,608 rows using sequential GUID.

-- Record id = 27063240
-- Windows
SET STATISTICS TIME On;

SELECT * FROM FactOnlineSales
WHERE seqguid = 'a53bb53e-f36b-1410-bba5-800000000000';

SET STATISTICS TIME Off;

-- Linux

SET STATISTICS TIME On;

SELECT * FROM FactOnlineSales
WHERE seqguid = '7541a8cb-17ec-e711-80c3-000d3a16ac77';

SET STATISTICS TIME Off;

Query 10. We are trying to retrieve one row from 12,627,608 rows using non-sequential GUID.

-- Record id = 27063240

-- Windows
SET STATISTICS TIME On;

SELECT * FROM FactOnlineSales
WHERE newguid = 'f22ff329-d2f5-4637-a5dd-847d8b77786b';

SET STATISTICS TIME Off;

-- Linux
SET STATISTICS TIME On;

SELECT * FROM FactOnlineSales
WHERE newguid = '2fea845d-3467-438b-8fae-49b8143c810e';

SET STATISTICS TIME Off;

Conclusion

From the result figures, it looks like SQL Server on Linux works as well as on Windows when reading data using Select while the Windows environment has better performance for SQL Server when it comes to writing; for example, restoring the database or updating the 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 the CPU on the machine takes to finish the job, and 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 environments, 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, the 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 enables 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 like 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.