Microsoft SQL Server 2019 Performance

Question

Hello,

I was wondering if anyone has had any performance issues with Microsoft SQL 2019 Express & Standard on Windows Server Standard 2022.

We have recently migrated a SQL application from Windows Standard 2016 with SQL Express 2017 to Windows Standard 2022 with SQL Express 2019 and we are seeing a slower experience between the two servers with the older server being faster.

The specifications of the servers are as follows

OLD SERVER

Intel(R) Xeon(R) CPU E3-1220 v6 @ 3.00GHz
4 x Cores
16Gb RAM
2 x 1TB 7.2K RPM SATA 6Gbps in a RAID 1 in a PERC H330 Integrated RAID Controller

NEW SERVER

Intel(R) Xeon(R) Silver 4216 CPU @ 2.10GHz
16 x cores
64Gb RAM

1. 2TB 10K RPM SAS 12Gbps 512n 2.5in in a RAID 1 in a PERC H350

--

Microsoft SQL has been installed locally both of the machines with their respective versions and when we run the following tests

• A large SQL query
• A full backup of the database (3.5Gb)

The tests are as follows

Large SQL query (returns 5 million results)

Old Server = 41 secs
New Server = 41 secs

Backup of database

Old Server = 125 Secs
New Server = 573 Secs (Transfer rate 6.114 mb/sec)

If I run the backup as a query which states the MAXTRANSFERSIZE (See below) then the backup runs at around 80 Secs with a transfer rate of around 35 Mb/sec but not sure why it is not doing this by default.

BACKUP DATABASE [database name]
TO DISK = 'C:\temp\test.bak'
WITH NOFORMAT, NOINIT, MAXTRANSFERSIZE = 4194304

It would suggest that there is some kind of I/O issue by default with Microsoft SQL.

Does anyone have any ideas what could be causing the slow performance?

Many thanks,

Luke

Answer 1

The new machine has only 70% of the CPU speed than the old. It has more cores, but that does not help you much when you have Express Edition.

What happens if you try to copy the backup file to a new location on the disk? What times does that take on the machines?

I would recommend that you use compression for the backups. This requires more CPU time, but reduces the size of what is being written to disk.

Answer 2

Erland,

Thanks for your response. I see that SQL Express has a limitation of 4 cores as per below

https://learn.microsoft.com/en-us/sql/sql-server/compute-capacity-limits-by-edition-of-sql-server?view=sql-server-ver16

I have installed SQL standard which has a limit of 24 cores and the query still takes the same time which is strange?

To answer your questions,

If I run the backup from SQL using the following command, it takes 64secs @ 54.877 MB/sec (but this fluctuates) but when I copy and paste the file, it can take 20 secs and negotiate between 100mbps - 1gbps.

Just don't understand why running the same query on the same database on EXPRESS and STANDARD return the same times. I would think that it would be quicker on STANDARD as it can take advantage of more cores?

Many thanks,

Luke

Answer 3

Only if you enable parallel operations will one query use multiple cores. The multiple cores allows multiple queries to run at the same time.

If I/O is slow then you probably did not make the cluster size 64k.

Answer 4

@Bruce (SqlWork.com) @Erland Sommarskog

Thanks for your help on this. So we have carried out some testing and there is a very large routine that when we run on the old server (Intel(R) Xeon(R) CPU E3-1220 v6 @ 3.00GHz 4 x Cores) using SQL EXPRESS the process takes 24 mins, however if I run the same process on the new server (Intel(R) Xeon(R) Silver 4216 CPU @ 2.10GHz) using SQL STANDARD then it takes 56 mins.

My logic is that with SQL STANDARD being licenced for 24 cores vs EXPRESS 4 cores then surely the new server would be quicker as it is using more cores albeit the core being slower?

When I run a query on STANDARD on the new server, I still only see one logical processor working. Is this expected behaviour?

Many thanks,

Luke

Answer 5

with your sql query, you will not see difference between standard & express as far as cpu.

standard should use more memory. but this may be little help on a single query as they both need to read the same pages. standard may be faster on a second query, but depends on cache reuse

again unless forced to parallel plan a single core is used for the query. see:

[https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16

[https://www.sqlshack.com/the-basics-of-parallel-execution-plans-in-sql-server/

I would first compare the i/o subsystems (raid system may be configured wrong for sqlserver):

[https://learn.microsoft.com/en-us/azure-stack/hci/manage/diskspd-overview