The MSSQL Standard edition, does it limit the number of Work Threads?

Question

Post reply

The MSSQL Standard edition, does it limit the number of Work Threads?

car_hack

SSC Enthusiast

Points: 173
More actions
July 1, 2019 at 9:56 pm

#3659108
I have a server with 128 logical processors, 64 bits server, and with a Standard edition of SQL Server 2014, and my question is this:
Considering that SQL Server connects an ERP with a high number of simultaneous connections (approximately 1,000), and the characteristic "Maximum calculation capacity" for each of the editions of SQL Server, the standard edition indicates 16 logical processors as the maximum supported, I understand that this has an impact on the "Workers Threads", that is, instead of supporting "4480", it is using only "704". Is what I'm saying about Workers Threads correct?
I leave the links with the features of SQL Server, and the configuration on the "Work Threads":
- https://docs.microsoft.com/en-us/sql/getting-started/features-supported-by-the-editions-of-sql-server-2014?view=sql-server-2014
- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

HansVE Mr or Mrs. 500 Points: 524 More actions · Answer 2

Why not just ask it?

SELECT max_workers_count
FROM sys.dm_os_sys_info;

PBarry SSC Enthusiast Points: 187 More actions · Answer 3

Hi!

The numbers 4480 and 704 are the default number of worker threads that SQL Server will create if you leave the configuration "Max worker threads" at 0 (4480 for 128 processors and 704 for 16 processors).

That being said, you can override this default behavior by specifying the number of worker threads you need. For instance, you can set the number at 1024 by running these commands:

exec sp_configure 'max worker threads', 1024;

reconfigure with override

Basically, you can set this number as high as 32767. BUT, you will probably run into troubles if you set it this high. On some of our SQL Server 2014 servers (Standard Edition), we have set it as high as 3072 (we have a lot of concurrent connections) without problems. You just need to make sure you have enough RAM to handle the worker threads.

Hope this helps.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

Enquiring minds want to know... with a monster system like what you have, why are you "cheaping out" with Standard Edition especially when you expect so many simultaneous connections?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

car_hack SSC Enthusiast Points: 173 More actions · Answer 5

Hello,

Thanks for your answers to all, but as you say Jeff Moden, to the conclusion that I need to get, is if I should recommend that we change to the Enterprise Edition, but I need to be certain to be able to give the indication of the change of SQL edition Server.