SQL 2019 express CPU license

Question

SQL 2019 express CPU license

Saran

Right there with Babe

Points: 760
More actions
November 30, 2021 at 4:35 pm

#3958723

Hi All,
Need you suggestion, our current system is 4 socket - 2 core = 8 CPU, I have read that express will have limited to lesser of 1 socket or 4 cores.
How can we utilize 4 cores. Can we go with 2 socket with 4 cores?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1359 More actions · Answer 1

I assume your description was of a physical server rather than a VM? 4-socket/2-core: Old Xeons or Opterons?

I believe you'll only get to use 2 cores -- SQL Server Express will ignore the other three CPU sockets. And the memory limitations for Express edition should be considered too. A four-socket server would generally have a lot of memory. Express will only use a fraction of that -- ~ 1 GB .

If SQL Server Express really is sufficient and you don't plan to upgrade anytime soon, you might want to run SQL Server Express in a VM to present only one socket w/ four cores. That way, you could still use the excess CPU & memory for other applications so the server resources aren't wasted. Or you might want to just use a single-socket server for SQL Server Express, and use the 4-socket server where the scalability is better suited.

Saran Right there with Babe Points: 760 More actions · Answer 2

Hi,

Thanks for your response. It is a VM runs on windows 2019 with SQL 2019, I believe it is IaaS in the could.

It is a small application and both DB and app are in same host. As of now we are good to run in express, due to license cost.

Windows2019

This reply was modified 2 years, 12 months ago by Saran.
This reply was modified 2 years, 12 months ago by Saran.
This reply was modified 2 years, 12 months ago by Saran.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

If you can only run express due to the cost then you can only run what express can support, sorry but that is the license terms you agreed to when you installed express.

If you want to use the full potential of the server your going to have to put your hand in your pocket and purchase Standard.

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

So you are limited to 2 cores as the 1 socket takes precedence and 1410MB of RAM.

You need more compute power you need to pay or switch the way the vCPUs are presented to the OS and SQL.

Saran Right there with Babe Points: 760 More actions · Answer 4

Hi,

Understood. If I go with CPU to 1 socket with 4 cores, will the SQL use 4 CPUs?

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 5

According to https://docs.microsoft.com/en-us/sql/sql-server/compute-capacity-limits-by-edition-of-sql-server?view=sql-server-ver15

Express

Limited to lesser of 1 socket or 4 cores

It should use 4 cores if you present them as 1 socket, 4 cores.

If you would present is as 2 socket with each 2 cores, it will use only 2 ( because the one socket limit )

Saran Right there with Babe Points: 760 More actions · Answer 6

Thank you. Got it now.

One final, doubt, if we increase 1 socket with 8 cores, I believe SQL will go for 1 socket.

This reply was modified 2 years, 12 months ago by Saran.

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

No sql will go for 4 cores.

The lesser of so you can have 1 socket with 4 cores max for express. No more.

Ant-Green SSC Guru Points: 113507 More actions · Answer 8

CPU isn’t going to be your bottleneck soon it’s going to be Max database size and RAM.

1410MB which express will use for the cache is nothing these days and the 10GB per database limit is ok when starting out but a few months go by you’ll be wanting more space.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 9

note that limit is per database - nothing preventing the developer to have 20 databases - and the big tables each on their own databases, and a master/driver database with synonyms on it to do all required sql joins in a single db. (there is at least 1 major software house I know of that does this to circumvent the db size for their clients)

cache is what will be the bottleneck depending on the type of queries issued.

Saran Right there with Babe Points: 760 More actions · Answer 10

Ant-Green wrote:

No sql will go for 4 cores.
The lesser of so you can have 1 socket with 4 cores max for express. No more.

Thank you, Client would go with 8 CPU, since it was pervious configuration. We just build new server to migrate, Now I have suggest to go with 1 socket with 4 cores from 2*4.

I will ask them to go with 1*8. So that SQL will use 4 cores and other 4 will be used for OS applications. Correct me if i am wrong.

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

If your following any cyber security rules it’s 1 server for 1 purpose.

So you never mix database and application on the same machine.

Saran Right there with Babe Points: 760 More actions · Answer 12

Ant-Green wrote:

If your following any cyber security rules it’s 1 server for 1 purpose.
So you never mix database and application on the same machine.

Ya, it was deigned like that. I cannot separate now. Can you answer the following.

I will ask them to go with 1*8. So that SQL will use 4 cores and other 4 will be used for OS applications. Correct me if i am wrong.

Ant-Green SSC Guru Points: 113507 More actions · Answer 13

No the application can use all 8 cores unless you can set some form of affinity in the application.

so if the app maxes out cpu0 then that’s 1 less cpu sql is able to use.

Saran Right there with Babe Points: 760 More actions · Answer 14

Thank you, if then I can limit 4 cores to SQL by affinity and 4 cores to applications.

https://www.sqlpassion.at/archive/2017/10/02/setting-a-processor-affinity-in-sql-server-the-unwanted-side-effects/