Below is the process involved in task executions of SQL Server.
max_workers_count on the
sys.dm_os_sys_info DMV shows the maximum number of workers that can be created, and
sys.dm_os_schedulers shows the number of workers that are active at any given time. If you monitor this value, you can find out the maximum numbers of workers that are really used by your SQL Server instance. For example, running the following on my test system with 16 logical processors shows 512 maximum workers but only 32 actives when the query was executed:
SELECT max_workers_count FROM sys.dm_os_sys_info SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers
A task is an execution request and represents the work that needs to be performed while a worker maps to an operating system thread (when the thread mode, the default, is used) or to a Windows fiber if Windows fibers (lightweight pooling) configuration option is used). Figure 1-1 shows the task execution process.
As mentioned earlier, in SQL Server, a scheduler runs in a non-preemptive mode, meaning that a task voluntarily releases control periodically and will run as long as it quantum allows it or until it gets suspended on a synchronization object (a quantum or time slice is the period of time for which a process is allowed to run). Under this model, threads must voluntarily yield to another thread at common points instead of being randomly context-switched out by the Windows operating system. The SQL Server code was written so that it yields as often as necessary and in the appropriate places to provide better system scalability.
The above figure shows that a task can also run in preemptive mode, but this will only happen when the task is running code outside the SQL Server domain; for example, when executing an extended stored procedure, a distributed query, or some other external code. In this case, since the code is not under SQL Server control, the worker is switched to preemptive mode and the task is not controlled by the scheduler. You can identify whether a worker is running in preemptive mode by looking at the
is _preemptive column of the
As indicated earlier, SQL Server by default will run in thread mode. An alternate choice is running in a Windows fiber mode. Running SQL Server in a Windows fiber mode is rarely recommended as it is only helpful for certain situations in which the context switching of the workers is the critical bottleneck in performance. In addition, common language runtime (CLR) execution is not supported under lightweight pooling. The lightweight pooling server configuration option, which defaults to 0, can be used to enable Windows fiber mode, for example, by running the following statement:
EXEC sp_configure ‘ lightweight pooling‘ , 1
Of course, if you try this in your test environment, don't forget to set it back to
0 before continuing. Looking at the
is_fiber column on the
sys.dm_os_workers DMV will show whether the worker is running using lightweight pooling.