Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests–what does it all mean?

1,to Quote: "Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests–what does it all mean?"

With this meditation I attempt to explain what some of the more common concepts that get used with SQL Server thread Manag Ement and scheduling are.

Parable: There was a all-powerful, but humble and benign Master, whom the workers revered and humbly served. The master accepted requests from other kingdoms and graciously agreed to grant all of them. To doing so the Master assigned tasks to He workers (servants) who completed them cooperating with each–allowing each othe R graciously to approach the Master one at a time.

Components:

Scheduler (SOS Scheduler), Haven object, manages thread scheduling in SQL Server and allows threads To is exposed to the CPU (described in sys.dm_os_schedulers). This was the all-powerful but benign and graceful master whom everyone abides.  He does not control things but lets th E workers work with each other and relies on their cooperation (co-operative scheduling mode). Each scheduler/master (one per logical CPU) accepts new tasks and hands them off to workers. SOS Scheduler allows one worker at a time to being exposed to the CPU.

The

Task –a task represents the work, needs to be performed (Sys.dm_os_tasks). A Task contains One of the following requests: query Request (RPC event or Language event), a prelogin R Equest (Prelogin event),  a login request (connect event), a logout request (disconnect event) , a query cancellation request (an Attention event), a bulk load request (bulk Load event), a Distri buted Transaction Request (transaction manager event). A task is, the Master is, About–it is, defines its existence. Note these is tracked at the SOS scheduler layer (thus dm_os_tasks)

Worker (worker thread) –this is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thr EAD). It is a structure within the Scheduler which maintains SQL server-specific information on what a worker thread does. Sys.dm_os_workers. Workers is the humble servants who carry out of the task assigned to them by the Master (scheduler).

thread –this is the OS Thread Sys.dm_os_threads, that's created via calls like CreateThread ()/_beginthreadex (). A Worker is an mapped 1-to-1 to a Thread.

Request is the logical representation of a query Request made from the client application to SQL Serve R (sys.dm_exec_requests). This query request have been assigned to a task, the scheduler hands off to a worker to process. This represents query requests as well as the system thread operations (like checkpoint, log writer, etc);  you would not F IND Login, Logouts, attentions and the like here. also, note that it's a representation at the SQL execution Engine level (thus dm_exec_requests) isn't at the SOS Scheduler layer.

Sessions –when The client application connects to SQL Server the sides establish a ' session ' on W Hich to exchange information. Strictly speaking a session is isn't the same as the underlying physical connection, it is a SQL Server logical Representati On a connection. Practical purposes, you can think of the this as being a connection (session =~ connection). See sys.dm_exec_sessions. This is the old SPID, the existed in SQL Server, and earlier. Sometimes notice a single session repeating multiple times in a DMV output. This happens because of parallel queries. A parallel query uses the same session to communicate with the client, but on the SQL Server side multiple worker (threads ) is assigned to service this request. So if you see multiple rows with the same session ID, know that the query request was being serviced by multiple threads.

Connections –this is the actual physical connection established at the lower protocol level with all of its char Acteristics sys.dm_exec_connections. There is a 1:1 mapping between a Session and a Connection.

Technical interconnection between the components:

A client application creates a physical connection to SQL Server. Then the application sends a PRE-LOGIN request and a task are created and assigned to a worker to fulfill. Once the server and client finish the Pre-login process, a login request is sent and another task is formed and handed off to a worker thread. Once The login is completed, SQL Server creates a session that represents this logical connection where it would exchange I Nformation with the client. When the client application sends a query request (or DTC or bulk load), the server again creates a tasks and assigns it to A worker thread for completion. If the query is cancelled in the middle of execution, for some reason, the server would receive an Attention request which 'll is a new task assigned to a worker for completion. If the query is allowed to complete, the hand, and the client application are done, it can send a disconnect or lo Gout request which again is packaged as a task and serviced by a worker.

2,to Quote a Post

The connection is the physical communication channel between SQL Server and the application:the TCP socket, the named Pip E, the shared memory region. The session in SQL Server corresponds to the Wikipedia definition of a session:a semi-permanent container Information exchange. In other words the sessions stores settings like cache of your login information, current transaction isolation level, SES Sion level SET values etc etc.

Normally there is one session in each connection, but there could being multiple session on a single connection (multiple Act Ive Result sets, MARS) and there is sessions that has no connection (SSB activated procedures, system sessions).

3,to quote:connections vs Sessions vs Requests:sql server–dynamic Management views

Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests–what does it all mean?