Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks,We can decide the no of threads using MAX_worker_threads option.
A value 0 means it can decide its max thread using below formula:
For 32 bit operating system:
Total available logical CPU’s <= 4 : max worker threads = 256
Total available logical CPU’s > 4 : max worker threads = 256 + ((logical CPUS’s - 4) * 8)
For 64 bit operating system:
Total available logical CPU’s <= 4 : max worker threads = 512
Total available logical CPU’s > 4 : max worker threads = 512 + ((logical CPUS’s - 4) * 16)
If you have set Max. Worker thread to 0, you can check the worker thread count calculated by SQL Server using the query
SELECT max_workers_count FROM sys.dm_os_sys_info
When a request is received, SQL Server will try to locate a idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created. Query below gives the amount of worker threads created at the moment in your SQL Server:
SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers
One of the main reason to to limit the max_worker_thread count to limit the resource used by each thread. In situations where the number of running tasks is less than the defined number of threads, each task will have its own dedicated thread. If the number of concurrent tasks rises beyond the number of threads, threads are pooled, and pending tasks are granted threads from the pool when available.
SQL Server can be configured so that threads will only be assigned to particular CPUs. This setting is typically used in NUMA-enabled systems, or on systems used for environments where a certain level of CPU resource needs to be reserved for other purposes, for example, other SQL Server instances or applications, to prevent one SQL Server instance from dominating CPU usage.
For a single instance server it is best suited to leave the setting as default, but for multi instance server it should be customized.
There are two types of AFFINITY mask:
Processor Affinity Mask and I/O Affinity Mask
In the default configuration these two options are checked. That means that all the CPUs will be used by SQL processor and I/O modules.
Physical core vs Logical core
To see how many cores are being used use below options:
1. See the startup message in error log for eg:
--using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2. select cpu_count from sys.dm_os_sys_info
3.Back to counting CPUs. To find out how many CPUs a particular instance is actually using, run the following query which is based on the sys.dm_os_schedulers DMV:
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
Physical socket : on a motherboard where a physical processor fits (used for licensing)
Physical core : within a physical processor (multi-core)
Logical core : within a physical core (hyper-threading)
Hyper-threading creates two “logical processors” within each physical processor core of an actual physical processor, that are visible to the operating system
Remember that Windows and SQL Server cannot tell the difference between physical and logical cores. Running the query below will tell you how many logical cores are
visible and how many physical CPUs you have.
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time FROM sys.dm_os_sys_info;
Maximum Degree of Parallelism (MAXDOP)
A commonly altered setting is Maximum Degree of Parallelism(MAXDOP), which controls the maximum number of CPUs that can be used in executing a single task or query. For example, a large query may be broken up into different parts, with each part executing threads on separate CPUs. Such a query is known as a parallel query.
By default, MAXDOP is 0, meaning that SQL Server is left to decide the appropriate number of CPUs to use. You can set this value to 1, effectively disabling parallel queries, or to a specific number that limits the number of CPUs that can be used.
In OLTP systems, use a maximum MAXDOP setting of 8, including systems with access to more than 8 CPU cores. The effort to split and rejoin a query across more than 8 CPUs often outweighs the benefits of parallelism
One of the downsides from setting MAXDOP to 1 is that certain operations, such as index rebuilds, benefit greatly from parallelism but are unable to do so with a
MAXDOP 1 setting. In such cases, you can specify the MAXDOP setting at a statement level. For example, the CREATE INDEX command, an example of which is shown here, accepts a MAXDOP parameter:
-- Use a MAXDOP hint to override the default server MAXDOP setting
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address] ([StateProvinceID] ASC)WITH (MAXDOP=0)GO
In this example, we specify MAXDOP = 0 to override the instance default MAXDOP setting, and thereby permit the index creation to be parallelized if SQL Server decides that's the best approach.
Best practice :
•For servers that use more than eight processors, use the following configuration:
•For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N
Note In this configuration, N represents the number of processors.
•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
•For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
•For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.