Scheduler&Task&Worker&Thread&Request&Session&Connection OF MSSQL

MSSQL一直以來被人們認為簡單、好學，但等到大家掌握了入門操作，深入理解起來又覺得非常的“擰巴”，尤其是對用慣了Oracle的同學來說，究其根本原因，無非是MSSQL引入和暴露了太多的概念、細節和理論層，而Oracle恰恰遮蔽了這些，比如下面講到的這些概念，即使是使用很久MSSQL的同學來說，也未必就真理解的正確，下面這段文字，很好的解釋了MSSQL中的幾個基本概念：

Scheduler (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.


Task –a task represents the work that 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 request (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 distributed transaction request (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)


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).


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.


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.


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 that existed in SQL Server 2000 and earlier. 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 request. So if you see multiple rows with the same session ID, know that the query request is being serviced by multiple threads.


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.tr