info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
SQL Server Connections - Schedulers, Threads, Workers, Tasks, Requests, Sessions, Connections, Users
by BF (Principal Consultant; Architecture; Engineering)
2015-08-05
MSSQL Connections Monitoring - Schedulers, Threads, Workers, Tasks, Requests, Sessions, Connections, Users
SQLOS - CPU scheduling:
High-level:
1. When an user connects to SQL Server a ConnectionID & SessionID are assigned to the user.
DMV: sys.dm_exec_connections, sys.dm_exec_sessions
2. Queries being executed by the User/Connection/Session are Requests
DMV: sys.dm_exec_requests and sys.dm_exec_sql_text(plan_handle)
3. Once the Execution Plan of a Query is generated, it is divided into one or more tTsks. The number of Tasks depends on Query Parallelism.
DMV: sys.dm_os_tasks
4. Each Task is assigned to a Worker. A Worker is where the work actually gets done.
Maximum number of workers (assigned to SQL Server) depends on the number of CPUs and hardware architecture (32 bit or 64 bit)
Further read: http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/14/max-worker-threads-and-when-you-should-change-it.aspx
DMV: sys.dm_os_workers
5. Each Worker is associated with a Thread.
DMV: sys.dm_os_threads
6. Scheduler schedules CPU time for a Task/Worker.
When SQL Server service starts, it creates one Scheduler for each logical CPU. (few more Schedulers for internal purpose).
During this period, Scheduler may keep a task in RUNNING or RUNNABLE or SUSPENDED state for various reasons.
DMV: sys.dm_os_schedulers
7. Once the Task is completed, all consumed resources are freed
Thread Scheduling - Waits:
Whenever a thread needs a resource that it can’t immediately acquire, it becomes suspended and waits on the Waiter List to be told (signaled) that its resource is available. The time spent on the Waiter List is the resource wait time and the time spent on the Runnable Queue is the signal wait time.
- SQL OS keeps track of the wait time and the signal wait time.
- The Waiter List is unordered (any thread on it can be signaled at any time and move to the Runnable Queue) and the Runnable Queue is First-In-First-Out (FIFO) almost 100% of the time.
- Another reason why a thread may need to move off the Processor – it exhausts its quantum. Voluntarily giving up the processor (known as yielding). The thread quantum in SQL OS is fixed at 4 milliseconds. When this occurs, the thread moves directly to the bottom of the Runnable Queue, as there is nothing for it to wait for. SQL OS must register a wait type for this transition off the Processor though, and registers SOS_SCHEDULER_YIELD. This behavior is often mistaken for CPU pressure, but it’s not – it’s just sustained CPU usage. CPU pressure, and recognizing it, is a whole other topic for a future post. As far as this post is concerned, as long as the average signal wait time is low (0-0.1-0.2ms), it’s a pretty safe bet that CPU pressure isn’t an issue. Reference
Scripts: (for testing - execute a user query in a loop & then execute all these queries together in a separate batch)
--Query 1: User Connection & Query as a Request
SELECT REQ.connection_id, REQ.database_id, REQ.session_id, REQ.command, REQ.request_id,
REQ.start_time, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 103 -- *****SPID of your query that is running*****
--Query 2: Request is divided into Task(s)
SELECT task.task_address, task.parent_task_address, task.task_state, REQ.request_id, REQ.database_id, REQ.session_id,
REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req INNER JOIN sys.dm_os_tasks task on req.task_address = task.task_address or req.task_address = task.parent_task_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 103
--Query 3: Each Task is assigned to a Worker (an available worker)
SELECT worker.worker_address, worker.last_wait_type, worker.state, task.task_address, task.parent_task_address,
task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id,
REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req INNER JOIN sys.dm_os_tasks task on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 103
--Query 4: Each Worker is associated with a Thread
SELECT thread.thread_address, thread.priority, thread.processor_group, thread.started_by_sqlservr, worker.worker_address,
worker.last_wait_type, worker.state, task.task_address, task.parent_task_address, task.task_state, REQ.request_id,
REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req INNER JOIN sys.dm_os_tasks task
on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address
INNER JOIN sys.dm_os_threads thread on worker.thread_address = thread.thread_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
WHERE req.session_id = 103
--Query 5: Schedulers associated with CPU schedules CPU time for Workers
SELECT sch.scheduler_address, sch.runnable_tasks_count, sch.scheduler_id, sch.cpu_id, sch.status as 'SchedulerStatus', thread.thread_address, thread.priority as 'thread priority', thread.processor_group,
thread.started_by_sqlservr as 'ThreadStartedByMSSQL', worker.worker_address, worker.last_wait_type, worker.state as 'WorkerState', task.task_address, task.parent_task_address,
task.task_state, REQ.request_id, REQ.database_id, REQ.session_id, REQ.start_time, REQ.command, REQ.connection_id, REQ.task_address, QUERY.text
FROM SYS.dm_exec_requests req INNER JOIN sys.dm_os_tasks task
on req.task_address = task.task_address or req.task_address = task.parent_task_address
INNER JOIN SYS.dm_os_workers WORKER ON TASK.task_address = WORKER.task_address
INNER JOIN sys.dm_os_threads thread on worker.thread_address = thread.thread_address
INNER JOIN sys.dm_os_schedulers sch on sch.scheduler_address = worker.scheduler_address
Cross apply sys.dm_exec_sql_text (req.sql_handle) as query
--WHERE req.session_id = 103
order by sch.scheduler_id asc
Tasks, Workers, Threads
Select
(Select max_workers_count from sys.dm_os_sys_info) as 'WorkerThreadsPossible',
SUM(active_Workers_count) as 'WorkerThreadsActive',
(select max_workers_count from sys.dm_os_sys_info) - SUM(active_Workers_count) as 'WorkerThreadsAvailable',
SUM(runnable_tasks_count) as 'WorkerThreadsWaitingForCPU',
SUM(work_queue_count) as 'TasksWaitingForWorkerThread'
from sys.dm_os_Schedulers
where status='VISIBLE ONLINE'
active_workers_count:
Number of workers that are active. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended. Is not nullable.
runnable_tasks_count:
Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable.
work_queue_count:
Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.
Active Requests Query:
SELECT
s.Session_id,
r.Status,
r.open_transaction_count as 'TRX_Open', --Number of transactions that are open for this request
r.blocking_session_id 'Blk by',
r.Wait_Type, --If the request is currently blocked, this column returns the type of wait.
r.Last_Wait_Type, --If this request has previously been blocked, this column returns the type of the last wait.
r.Wait_Resource, --If the request is currently blocked, this column returns the resource for which the request is currently waitin
r.Wait_Time / (1000.0) 'Wait Sec', --If the request is currently blocked, this column returns the duration in milliseconds, of the current wait.
r.CPU_Time, --CPU time in milliseconds that is used by the request
r.Logical_Reads,
r.Reads,
r.Writes,
r.total_elapsed_time / (1000.0) 'Elaps Sec', --otal time elapsed in milliseconds since the request arrived
db_name(r.database_id) as DBName,
'"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS Statement_Text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid))
+ N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'')+'"' AS Command_Text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
r.blocking_session_id,
s.session_id
Lists runnable queries:
Runnable queues are those queues waiting for CPU time. Signal waits are the time spent in the runnable queue waiting for the CPU.
select scheduler_id, session_id, status, command from sys.dm_exec_requests where status = 'runnable' and session_id > 50 order by scheduler_id
Configurations:
max worker threads (Configurable)
Use max worker threads option to configure the number of worker threads available to Microsoft SQL Server processes.
SQL Server uses the native thread services of the O.S. so that one or more threads support each network that
SQL Server supports simultaneously, another thread handles database checkpoints and a pool of threads
handles all users.
Thread pooling helps optimize performance when large numbers of clients are connected to the server.
Usually, a separate operating system thread is created for each query request. However, with hundreds of
connections to the server, using one thread per query request can consume large amounts of system resources.
The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number
of query request, which improves performance.
The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker
threads at startup. This setting is best for most systems; however, depending on your system configuration, setting
max worker threads to a specific value sometimes improves performance.
When the actual number of query request is less than the amount set in max worker threads, one thread handles each query request.
However, if the actual number of query request exceeds the amount set in max worker threads, SQL Server pools the worker threads
so that the next available worker thread can handle the request.
When all worker threads are active with long running queries, SQL Server may appear unresponsive until a worker thread completes
and becomes available. Though not a defect, this can sometimes be undesirable. If a process appears to be unresponsive and no new
queries can be processed, then connect to SQL Server using the dedicated administrator connection (DAC), and kill the process.
To prevent this, increase the number of max worker threads.
*Each CPU (logical or physical) is assigned a scheduler. Only one thread can be running on a CPU at any given moment. Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
Monitoring:
sys.dm_os_threads (Transact-SQL)
Returns a list of all SQL Server Operating System threads that are running under the SQL Server process.
SELECT * FROM sys.dm_os_threads WHERE started_by_sqlservr = 1;
SELECT * FROM sys.dm_os_threads WHERE started_by_sqlservr = 0;
started_by_sqlservr field:
Indicates the thread initiator.
1 = SQL Server started the thread.
0 = Another component started the thread, such as an extended stored procedure from within SQL Server.
Upon startup, SQL Server starts threads and then associates workers with those threads. However, external
components, such as an extended stored procedure, can start threads under the SQL Server process. SQL Server
has no control of these threads. sys.dm_os_threads can provide information about rogue threads that consume
resources in the SQL Server process.
sys.dm_os_workers (Transact-SQL)
Returns a row for every worker in the system.
select state, count(*) from sys.dm_os_workers group by state
select * from sys.dm_os_workers
state field:
Worker state. Can be one of the following values:
INIT = Worker is currently being initialized.
RUNNING = Worker is currently running either nonpreemptively or preemptively.
RUNNABLE = The worker is ready to run on the scheduler.
SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.
SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers
sys.dm_os_schedulers (Transact-SQL)
Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor.
Use this view to monitor the condition of a scheduler or to identify runaway tasks.
sys.dm_exec_requests (Transact-SQL)
Returns information about each request that is executing within SQL Server.
sys.dm_exec_sessions (Transact-SQL)
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information
about all active user connections and internal tasks. This information includes client version, client program name, client
login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load
and to identify a session of interest, and then learn more information about that session by using other dynamic management
views or dynamic management functions.
The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table.
sys.dm_exec_connections (Transact-SQL)
Returns information about the connections established to this instance of SQL Server and the details of each connection.
sys.dm_os_sys_info (Transact-SQL)
Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.
max_workers_count - Represents the maximum number of workers that can be created. Not nullable.
sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available
Messages from SQL Server re: Threads:
select * from sys.messages where language_id = 1033 and text like '%Thread%'
|
|
|
|
|
|
|
|
|
|