![]() If the job object is reconfigured to use CPU 1 and 2 the sys.dm_os_schedulers data remains the same and CPU resource assignment is handled by the OS. The sys.dm_os_schedulers are 0 and 1 with NO_AFFINITY set to allow workers to run on any presented CPU from the job object. even though the workers are scheduled on other CPUs by the OS.įor example: 2 CPU Slo with Job Object configured to use CPU 4 and 5. The workers and schedulers are not impacted because the Operating System simply schedules execution on the configured CPUs. By only creating the online schedulers the overhead of the offline schedulers ( ~8MB per scheduler) is eliminated.īy not affinitizing the workers to specific CPUs the job object can be reconfigured to use different CPUs. NO_AFFINITY only creates online schedulers. ![]() ![]() The workers assigned to a scheduler are allowed to use any online CPU presented and the workers are not affinitized (mask remains 0.) Refer to the SetThreadAffinityMask / SetThreadGroupAffinity APIs for more details as well as Processor Groups. If the job configuration is changed to use CPU 0 and 1 ( 00000011) and SQL Server brings scheduler 0 and 1 online and takes scheduler 2 and 3 offline. The sys.dm_os_schedulers would show 8 schedulers but only schedulers 2 and 3 are set to visible, online and the other scheduler are set to visible, offline. The affinity mask for the job would be 00001100 indicating CPU 2 and3 are to be used for processes running in this job context. For this example, assume the host machine is single node, 8 CPU system. The job object can be configured to limit the resource usage on the system, including which nodes and CPUs the processes started under its control may utilize.įor example: A smaller sized, SQL Azure database, using 2 CPUs, could be assigned to CPU 2 and 3 on the host machine. In SQL Azure the SQL Server may be executed under the control of a Windows Job Object. (Usually queries submitted from TDS based clients.)Ī hidden scheduler only accepts internal requests.įor example: When performing a backup, the striped file activities are handled by tasks on hidden scheduler. The scheduler is accepting external requests. The SQL Server schedulers can be visible or hidden. This allows you to scale up or down the SQL Server schedulers without restarting the SQL Server. Then migrate the workers to online schedulers. While, draining workers from offline scheduler’s the worker’s affinity mask is adjusted to use any online scheduler’s CPU, allowing SQL Server to stop using the associated offline CPU and complete the active tasks. The scheduler’s workers are not moved to online schedulers until the current task completes. The SQL Server schedulers can be online or offline.Īn online scheduler IS accepting requests.Īn offline scheduler is NOT accepting requests.Ī scheduler dynamically may transition from online to offline. A memory node may have 1 or more scheduling nodes, but a scheduling node can only be assigned to a single memory node. SQL Server aligns schedulers and other partitioned structures with the memory node to reduce access to remote, NUMA node memory when possible. The decision is performance driven.Ī memory node represents the memory associated with a group of CPUs from the physical hardware. The Soft Numa feature may take the 64 CPUs and create 8 scheduler nodes, each managing 8 CPUs or 4 scheduler nodes managing 16 CPUs, etc. SQL Server may choose to divide the memory node allowing for better partitioning and performance. Can be configured to use a subset of the CPUs presented by the OS from the same memory node.įor example: A memory node with 64 CPUs is a complete, Operating System, scheduler group. SQL Server makes a specific distinction between scheduling and memory nodes.Ī scheduling node is a used to group a set of SQLOS schedulers. Before explaining the different scheduler affinity types let me clarify some terminology. SQL Server uses 3 types of affinity to control where the SQL Server worker threads execute.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |