[SOLVED] SQL Server 2019 Insert Performance: CentOS 7 vs Windows 10

Issue

We have a small sample export (~100k rows) of a database. The format is a .sql file with Insert statements in each row:

Set Identity_Insert dbtable ON
Insert into dbtable (Id,Name) values(1,N'dummy');
... repeated 1k rows
Set Identity_Insert dbtable OFF
GO

Apart from this not being the best practice for exporting/importing, we observed major performance differences between SQL Server 2019 running on Linux (CentOS 7) vs. running on Windows 10. Both SQL Servers were freshly setup with the latest installer and used out of the box, so no particular settings or optimizations done.

Time for 100k Inserts
CentOS 7: ~330 seconds
Windows 10: ~17 seconds

Regarding the compute power, the CentOS system should be way more powerful, running on a HPC cluster with 16 cores, 32 GB memory and high bandwidth storage cluster. The Windows system is a typical office Laptop (i7, 16 GB, SSD).

Any ideas where to start troubleshooting? Are there major differences in the base installation / default setup of SQL Server between the different OS?

Solution

Each insert is a separate autocommit transaction by default. SQL Server will flush the transaction log buffer to disk after every insert to harden the transaction. The time it takes for these physical writes (100K in this case) will vary greatly depending on IOPS capabilities of the storage subsystem. It is not uncommon that Local SSD, even on a PC, might outperform enterprise grade SAN storage for a sequential write IOPS workload because SAN latency is higher than a local PCI bus.

Batching the inserts in large transactions will reduce the number of physical transaction log writes and speed up the insert script considerably.

Answered By - Dan Guzman