Partilhar via

SQL Server Worker Thread Default Calculation

  • Artigo

SQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments.   When running on smaller environments SQL Server reduces the worker target.

For an X64 installation, using the sp_configure ‘max worker threads’ value of 0 SQL Server uses the following calculation.

defaults

On small environments SQL Server always uses 256 as the target.

Running on a larger environment 512 workers are the default and are adjusted based on CPUs count.

1632

When more than 4 CPUs are present additional the worker limit is increased from the 512 worker level to 512 plus the remaining CPUs times 32 or 16.  The additional worker level is increased by 32 workers per CPU when the system presents 64 or more CPUs.

The design takes into account smaller environments which few CPU and Memory resources as well as larger CPU and Memory systems.

The worker calculation only applies to visible schedulers.  Hidden schedulers and the Dedicated Admin Connection (DAC) schedulers are not impacted by the calculation.

Bob Dorr

Comments

  • Anonymous
    February 13, 2019
    Hi,Good change, simple and important.Hugs.