SQLServe中的Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests

觉得这篇文章解释的很好，原文地址：https://techcommunity.microsoft.com/t5/sql-server-support-blog/tasks-workers-threads-scheduler-sessions-connections-requests/ba-p/333990

1. 概叙

With this meditation I attempt to explain what some of the more common concepts that get used with SQL Server thread management and scheduling are.
通过这次思考，我尝试解释 SQL Server 线程管理和调度中使用的一些更常见的概念。

Parable: There was an 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 do so the Master assigned tasks to his workers (servants) who completed them cooperating with each – allowing each other graciously to approach the Master one at a time.
寓言：曾经有一位全能而谦逊、仁慈的主人，工人们都对他充满敬畏，谦卑地为他服务。这位主人接受来自其他王国的请求，并慷慨地同意满足它们。为了实现这一点，主人分配了任务给他的工人（仆人），他们合作完成这些任务，互相彼此容许着，优雅地一个接一个地接近主人。

2. Components:

2.1Scheduler (SOS Scheduler)

– the object that manages thread scheduling in SQL Server and allows threads to be exposed to the CPU (described in sys.dm_os_schedulers ). This is the all-powerful but benign and graceful master whom everyone abides. He does not control things but lets the 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 be exposed to the CPU.

Scheduler (SOS Scheduler) – 管理 SQL Server 中的线程调度并允许线程暴露给 CPU 的对象（在 sys.dm_os_schedulers中描述）。 这就是万能而又仁慈、优雅、人人都尊崇的主人。 他不控制事物，而是让worker相互协作并依靠他们的协作（协作调度模式）。 每个scheduler/master（每个逻辑 CPU 一个）接受新任务并将其交给worker。 SOS Scheduler 允许一次让一个worker分派到CPU。

2.2Task

–a task represents the work that needs to be performed ( sys.dm_os_tasks ). A task contains one of the following events: query (RPC event or Language event), a prelogin (prelogin event), a login (connect event), a logout (disconnect event), a query cancellation (an Attention event), a bulk load (bulk load event), a distributed transaction (transaction manager event). A task is what the Master is about – it is what defines its existence. Note these are tracked at the SOS scheduler layer (thus dm_OS_tasks)
Task – 一个task代表需要执行的工作 ( sys.dm_os_tasks )。 task包含以下事件之一：query（RPC 事件或language事件）、prelogin（预登录事件）、login（连接事件）、logout（断开连接事件）、query cancellation（Attention事件）、bulk load（批量加载事件），分布式事务（事务管理器事件）。 一个task是主人所关注的事情，它定义了主人的存在。注意，这些任务在SOS调度器层面进行跟踪（因此在dm_OS_tasks中）。

2.3 Worker (worker thread)

– This is the logical SQL Server representation of a thread (think of it as a wrapper on top of the OS thread). It is a structure within the Scheduler which maintains SQL Server-specific information about what a worker thread does. sys.dm_os_workers . Workers are the humble servants who carry out the task assigned to them by the Master (scheduler).
Worker（工作线程）——这是线程的 SQL Server 逻辑表示（将其视为OS线程之上的包装器）。 它是调度程序中的一个结构，用于维护有关工作线程执行操作的 SQL Server 特定信息。 sys.dm_os_workers 。 工人是卑微的仆人，执行主人（调度者）分配给他们的任务。

2.4 Thread

– this is the OS thread sys.dm_os_threads that is created via calls like CreateThread() / _beginthreadex() . A Worker is mapped 1-to-1 to a Thread.
Thread - 这是通过调用CreateThread() / _beginthreadex()等方法创建的操作系统线程，由sys.dm_os_threads跟踪。每个worker与一个thread一一对应。

2.5 Request

Request is the logical representation of a query request made from the client application to SQL Server ( sys.dm_exec_requests ). This query request has been assigned to a task that the scheduler hands off to a worker to process. This represents query requests as well as system thread operations (like checkpoint, log writer, etc); you will not find login, logouts, attentions and the like here. Also, note that this is a representation at the SQL execution engine level (thus dm_EXEC_requests) not at the SOS Scheduler layer.
Request 是从客户端应用程序向 SQL Server ( sys.dm_exec_requests ) 发出的query request的逻辑表示。 该查询请求已分配给scheduler交给worker处理的task。 这代表查询请求以及系统线程操作（如检查点、log writer等）； 您不会在这里找到登录、注销、attentions等信息。 另请注意，这是 SQL 执行引擎级别（即 dm_EXEC_requests）的表示，而不是 SOS 调度程序层的表示。

2.6 Sessions

– when the client application connects to SQL Server the two sides establish a “session” on which to exchange information. Strictly speaking a session is not the same as the underlying physical connection, it is a SQL Server logical representation of a connection. But for practical purposes, you can think of this as being a connection (session =~ connection), see sys.dm_exec_sessions . This is the old SPID (session process id) that existed in SQL Server 2000 and earlier. In the case of system sessions (internal sessions spawned by SQL Server like LazyWriter, Checkpoint, Log Writer, Service Broker, etc), no external physical connection is mapped to the session. Only a session_id exists. Typically reserved for session IDs < 50, but can be a higher value.
Session– 当客户端应用程序连接到 SQL Server 时，双方会建立一个“session”来交换信息。 严格来说，session与底层物理连接不同，它是连接的 SQL Server 逻辑表示。 但出于实际目的，您可以将其视为连接（session =~ connection），请参阅 sys.dm_exec_sessions 。 这是 SQL Server 2000 及更早版本中存在的旧 SPID（会话进程 ID）。 对于系统会话（由 SQL Server 生成的内部会话，如 LazyWriter、Checkpoint、Log Writer、Service Broker 等），没有外部物理连接映射到会话。 仅存在一个 session_id。 通常为会话 ID < 50 保留，但也可以是更高的值。

You may 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 are assigned to service this query request. So if you see multiple rows with the same session ID, know that the query request is being serviced by multiple threads. A session_id (SPID) is used to identify the work performed inside SQL Server. For example, you may want to find out which session_id is executing a query, or which session_id has its query cancelled. Some of the properties of a sesssion include login time, last request (query) time, CPU consumed , memory used, and total elapsed time used by a query or set of queries on this session, user name, SET options configured for this session.
您有时可能会注意到单个会话在 DMV 输出中重复多次。 发生这种情况是因为并行查询。 并行查询使用相同的会话与客户端通信，但在 SQL Server 端分配多个worker线程来服务该查询请求。 因此，如果看到多行具有相同的Session ID，则表明查询请求正在由多个线程提供服务。 session_id (SPID) 用于标识 SQL Server 内部执行的工作。 例如，可能想要找出哪个 session_id 正在执行查询，或者哪个 session_id 的查询被取消。 会话的一些属性包括登录时间、上次请求（查询）时间、消耗的 CPU、使用的内存以及此会话上的一个查询或一组查询使用的总运行时间、用户名、为此会话配置的 SET 选项。

2.7 Connections

– this is the actual physical connection established at the lower protocol level with all of its characteristics sys.dm_exec_connections . There is a 1:1 mapping between a Session and a Connection. A connection has some of the following properties - protocol (Shared Memory, TCP, Named Pipes), authentication type (NTLM, Kerberos), Encryption (on or off), Network packet size, client IP address and port. These are all physical properties of the connection. A connection_id in sys.dm_exec_connections is a GUID and is used to uniquely identify that physical connection. You typically won’t use a connection_id to identify which session is executing a query; a session_id is used instead.

Connection – 这是在较低协议级别建立的实际物理连接，具有 sys.dm_exec_connections 的所有特征。 Session 和 Connection 之间存在 1:1 的映射。 连接具有以下一些属性 - 协议（共享内存、TCP、命名管道）、身份验证类型（NTLM、Kerberos）、加密（打开或关闭）、网络数据包大小、客户端 IP 地址和端口。 这些都是连接的物理属性。 sys.dm_exec_connections 中的connection_id 是一个GUID，用于唯一标识该物理连接。 通常不会使用connection_id 来识别哪个会话正在执行查询；而是使用connection_id 来识别哪个会话正在执行查询。 而是使用 session_id。

2.8 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 is 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 will exchange information with the client. When the client application sends a query request (or DTC or bulk load), the server again creates a task and assigns it to a worker thread for completion. If the query is cancelled in the middle of execution, for some reason, the server will receive an Attention request upon which the IOCP listener will mark a bit that the query is cancelled and the worker that was running the query would stop executing when it sees the bit. If the query is allowed to complete, on the other hand, and the client application is done, it can send a disconnect or logout request which again is packaged as a task and serviced by a worker.
一个客户端应用程序创建到 SQL Server 的物理connection。 然后，应用程序发送prelogin(预登录）request，并创建一个task并将其分配给worker来完成。 一旦服务器和客户端完成(prelogin)过程，就会发送login request，并形成另一个task并将其移交给worker。 登录完成后，SQL Server 将创建一个表示此逻辑连接的session，在该会话中它将与客户端交换信息。 当客户端应用程序发送query request（或DTC或批量加载）时，服务器再次创建task并将其分配给worker来完成。 如果query在执行过程中由于某种原因被取消，服务器将收到一个 Attention request，IOCP 侦听器将在该request上标记查询已取消的bit位，当执行这个query的worker看到这个bit位时将停止执行。另一方面，如果查询被允许完成，而客户应用程序已经完成，它可以发送一个disconnect或logout request，这个请求同样被打包为一个任务，然后由一个工作线程来处理。
可以看到，request贯穿于各个阶段，如：连接阶段的prelogin、login，session阶段的query request、attention request、以及完成阶段的disconnect 或logout request