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 management and scheduling are.

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.



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.

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.



Comments (18)
  1. Vijay says:

    Very nice. Clear and concise. Thanks!!

  2. Nathan says:

    This is the best, most concise explanation the important concepts of the SQL OS.  Your example also ties things together in a very efficient, exact way.  I love the parable too!  Kudos to you!!!

  3. P says:

    This is just the best ever explanation of these important concepts. Thanks.

  4. Lavisa says:

    Thank you for your work, this little note helped me to understand huge detailed article on the same theme and saved me a lot of time.

  5. dan says:

    Great job!!!! Thank you Mr. Jo.Pi. got your blog?

  6. graham says:


  7. Zev Spitz says:

    http://dba.stackexchange.com/a/13700/48393 — “Normally there is one session on each connection, but there could be multiple session on a single connection (MARS) and there are sessions that have no connection (SSB activated procedures, system sessions). There are also connections w/o sessions, namely connections used for non-TDS purposes, like database mirroring sys.dm_db_mirroring_connections or Service Broker connections sys.dm_broker_connections.”

    1. Joseph.Pilov says:

      Thank you, Zev. Regarding the MARS scenario, “multiple results sets” is different from “multiple sessions”. The result set represents each set of data that the client application receives from the server as a result of executing a query. Therefore, the server does not need to open multiple sessions to produce multiple result sets. I actually built a simple client application and tested it, and indeed only a single session is used on the SQL Server side to produce the results despite “MultipleActiveResultSets=true”.
      Regarding “There are also connections w/o sessions, namely connections used for non-TDS purposes, like “, you bring up a good point that sometimes there sessions and connections are not equal to each other. However, as far as the SQL Server terminology described in this post is concerned, it is the other way around. Namely, there could be sessions without connections. A physical connection is only initiated by a client application, whereas a session may be initiated internally by SQL Server. Therefore, there are system sessions for things like LazyWriter, Log Writer, Resource Monitor, Service Broker.

      select s.session_id, r.command, r.wait_type, * from sys.dm_exec_sessions s join sys.dm_exec_requests r
      on s.session_id = r.session_id
      where s.session_id < 51

      However, there are no connections opened by any external applications to the SQL Server to execute those sessions.

      select * from sys.dm_exec_sessions s join sys.dm_exec_connections c
      on s.session_id = c.session_id
      where s.session_id < 51

      1. Rabindra Ghimire says:

        Joe, thanks for your wonderful article.
        I got a question.
        Can there be a scenario/example where there would be a one to many relationship between a session and a connection?


        1. Joseph.Pilov says:

          Rabindra, thanks for your question. I am not sure what scenario would lead to such a relationship. I have looked at outputs from thousands of servers and have never seen one. The question is why is this important and have you seen one? I suspect most people think about such a relationship when they think about worker threads and sessions. The parallel query scenario in this blog describes such a scenario. Don’t forget that a session is a logical structure in SQL Server and is not the representation of a physical connection. So take it as it is.

  8. DB Ramesh says:

    Thanks Joe for gr8 article

  9. prem says:

    how much is the maximum allowed session in sql server ?

    1. Joseph.Pilov says:

      Prem, you cannot control sessions in SQL Server, the server decides that. I have not investigated the code for any limits, but I doubt there is a limit imposed. You are probably thinking more about connection limits, which is what you control and is configurable: https://msdn.microsoft.com/en-us/library/ms187030.aspx

  10. Jason Holladay says:

    A MARS session can (shall?) have multiple connections associated with it. See documentation on sys.dm_exec_connections. In our environment, a MARS session appears to always have multiple connections even when the application isn’t knowingly taking advantage of MARS functionality. We’ve found that on a stressed machine, this can cause serious problems and that disabling MARS on the connections cleared the problems up. I use multiple connections for a session in sys.dm_exec_connections to identify these MARS sessions. I’d also like to determine whether there is any relationship between connections in sys.dm_exec_connections and parallel execution, but can’t find any relationship documented between the two (so far).

  11. Kent says:

    When trying to diagnose issues regarding limits on sessions and workers, a handy query is to query against [sys].[resource_stats], it shows you in five minute increments what percentage of your worker and session policies that you are hitting.

  12. raghu machineni says:

    Joseph.Pilov I am very much thankful to you and all.

  13. Another DBA says:

    Thank you, Mr. Pilov. Definitions of each, are perfectly crisp and clear.

    Quick question: Could you please, lay down all the components in a sequence in the way they will be triggered when a user wants to execute a query? Just trying to visualize. (One of my Colleague asked me the same question and I stuttered. ;-))

    Thank you.

    1. Joseph.Pilov says:

      Does the “Technical Interconnection between the Components” section help answer this? Or is there some other sequence that you are looking for. If the latter, please elaborate. Thanks.

Comments are closed.

Skip to main content