gotchasqlMinor
Difference between worker threads and sessions in SQL Server
Viewed 0 times
serversqlthreadsworkerdifferencebetweenandsessions
Problem
I'm looking at Azure Premium/P3 database offering under "Overview of the Performance Model" on http://msdn.microsoft.com/en-US/library/azure/dn741336.aspx
What's the difference between worker threads and sessions? Am I getting it right when I think there could be at most 16000 client machines opening a concurrent session to the same database but only 1600 queries can be executed concurrently?
What's the difference between worker threads and sessions? Am I getting it right when I think there could be at most 16000 client machines opening a concurrent session to the same database but only 1600 queries can be executed concurrently?
Solution
It depends on the session's execution plan. If a session executes a query with a serial plan then yes, you can say 1600 queries, but if it runs a query with a parallel plan, then a session can have multiple worker threads per session.
Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?
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).
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
Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?
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).
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
Context
StackExchange Database Administrators Q#64112, answer score: 5
Revisions (0)
No revisions yet.