Task Execution Process in SQL Server

DZone 's Guide to

Task Execution Process in SQL Server

Learn how to easily set up, operate, and scale Microsoft SQL Server deployments in the cloud, as well as how to perform task executions of SQL Server.

· Database Zone ·
Free Resource

Microsoft SQL Server is a relational database management system developed by Microsoft. Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the cloud.

Below is the process involved in task executions of SQL Server.

The max_workers_count on the sys.dm_os_sys_info DMV shows the maximum number of workers that can be created, and active_worker_count from 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.

Image title

Figure 1-1

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 sys.dm_os_workers DMV.

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.

amazon rds, database, rdbms, sql server, task execution, tutorial

Published at DZone with permission of Micah Williams . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}