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

Hello Joseph_Pilov ,

This is a wonderful write-up and it has brought me much closer to understanding the fundamentals of Sessions vs Connections. While reading your original post, I was cross referencing the Microsoft Docs and I was wondering if you could help me better understand one of your points.

Regarding a session being potentially broken up into multiple rows due to what you called a "parallel query", would you happen to have some example which I can run that will result in multiple sessions in the sessions DMV for demonstration purposes? Is your term "parallel query" the same as "parallel processing" on a single query statement, or something different?

Also, I just wanted to mention that in my research and cross-referencing I found that https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15#relationship-cardinalities has a potential to have a one-to-zero relationship to connections, meaning a session does not necessarily have to be associated with a connection. This wasn't mentioned in your post but I thought it would make a great addition here. The only example I could locate of this are what are referred to as "https://docs.microsoft.com/en-us/archive/blogs/psssql/how-it-works-system-sessions". I don't understand what these are but based on the name, I assume these are sessions required for the system running the SQL Server instance to operate that are separate from any user connection.

Here's a query to view those: 

SELECT *
FROM sys.dm_exec_sessions 
WHERE is_user_process = 0;

Thanks for your great post!