Thread:
--------------
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.
AFFINITY
MASK:
-----------------
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:
MAXDOP=8
•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.
