3/4/2023 0 Comments Process affinity not being setImagine now you want to restrict which specific CPU cores your SQL Server instance should use. With this example you can see quite easily how a Scheduler moves around between the individual CPU cores. The binary value 1111 just means that the Scheduler of this query can run on each core of our 4 core system. With the default configuration of SQL Server, and a 4 core system, you will get back the value 15 – which is in binary 1111. JOIN sys.dm_os_threads t ON t.worker_address = w.worker_address JOIN sys.dm_os_workers w ON w.task_address = r.task_address SELECT r.session_id, t.affinity FROM sys.dm_exec_requests r With the following query you can also check on which CPU cores a specific query (based on the SPID) can be executed. The following picture shows you the CPU utilization during the execution of a Single-Threaded query which is mostly CPU bound.Īs you can see all 4 cores have some CPU utilization, but there is no CPU core that runs continuously at 100%. There is no 1:1 mapping between a Scheduler and a CPU core. Therefore, when an incoming query is assigned to a Scheduler, the Scheduler can be run on each CPU core. The following picture illustrates this very important concept. Each Scheduler can run on every CPU core. The most important point is now that a Scheduler is NOT bound to a specific CPU core. This Query State Machine is very important, because it is also tracked by the Wait Statistics, which give you a great overview why SQL Server is slow. For each CPU core you get a so-called Scheduler, which implements a Query State Machine with the states RUNNING, SUSPENDED, RUNNABLE. If you run SQL Server with a default configuration, SQL Server will schedule your queries on all available CPU cores. Today I want to talk about a very important topic in SQL Server – setting a Processor Affinity – and the unwanted side effects that you introduce with this approach. First of all I want to talk a little bit about what a Processor Affinity is, and how it relates to SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |