---------------------- SQLOS-related queries, SQLSERVER threads, schedulers,

-- SELECT * FROMSYS. dm_ OS _schedulers -- each scheduler of SQLSERVER -- selectaffinityfromsys. dm_ OS _workers -- select * fromsys for each SQLSERVER job. dm_ OS _threads -- List of all SQLOS threads in the SQLSERVER process -- select * fromsys. dm_ OS _tasks -- S

-- SELECT * from sys. dm_ OS _schedulers -- each scheduler of SQL SERVER -- select affinity from sys. dm_ OS _workers -- select * from sys. dm_ OS _threads -- List of all SQLOS threads in the SQL SERVER process -- select * from sys. dm_ OS _tasks -- S

-- SELECT * from sys. dm_ OS _schedulers -- each scheduler of SQL SERVER
-- Select affinity from sys. dm_ OS _workers -- each task of SQL SERVER
-- Select * from sys. dm_ OS _threads -- List of all SQLOS threads in the SQL SERVER process
-- Select * from sys. dm_ OS _tasks -- each active task on the SQL SERVER instance
-- Select * from sys. dm_ OS _waiting_task -- Information about the task queue waiting for resources

-- The scheduler monitor of the SQL SERVER task
SELECT
T1.session _ id,
CONVERT (varchar (10), t1.status) as status,
CONVERT (varchar (10), t1.command) as command,
CONVERT (varchar (10), t2.state) as work_state,
T1.task _ address
FROM
SYS. dm_exec_requests t1 join sys. dm_ OS _workers t2
On
T1.task _ address = t2.task _ address
Where
T1.command = 'Resource monitor'

-- Query the status of the job threads and tasks output by a cross-planning program in SQL Server
SELECT
Scheduler_id,
Cpu_id,
Parent_node_id,
Current_tasks_count,
Runnable_tasks_count,
Current_workers_count,
Active_workers_count,
Work_queue_count
FROM sys. dm_ OS _schedulers

/* ------- Description ---------------
Scheduler ID <1048576.
The scheduler with ID> = 1048576 is called a hidden scheduler.
Scheduler 255 represents a dedicated administrator connection (DAC ).
Each instance has a DAC scheduler.
When runnable_tasks_count returns 0, it indicates that no current task is running. However, there may be active sessions.
Active_workers_count indicates all worker threads that have associated tasks and are running in non-preemptive mode. Some tasks (such as network listeners) run under the preemptive plan.
The hidden scheduler does not process typical user requests. DAC scheduler exceptions.
*/

-- Query the working thread status
SELECT
Scheduler_id,
Cpu_id,
Current_tasks_count,
Runnable_tasks_count,
Current_workers_count,
Active_workers_count,
Work_queue_count
FROM
Sys. dm_ OS _schedulers
WHERE
Scheduler_id <255

-- Time when the worker thread has been running in the SUSPENDED or RUNNABLE state
SELECT
T1.session _ id,
CONVERT (varchar (10), t1.status) AS status,
CONVERT (varchar (15), t1.command) AS command,
CONVERT (varchar (10), t2.state) AS worker_state,
W_suincluded =
CASE t2.wait _ started_ms_ticks
WHEN 0 THEN 0
ELSE
T3.ms _ ticks-t2.wait _ started_ms_ticks
END,
W_runnable =
CASE t2.wait _ resumed_ms_ticks
WHEN 0 THEN 0
ELSE
T3.ms _ ticks-t2.wait _ resumed_ms_ticks
END
FROM sys. dm_exec_requests AS t1
Inner join sys. dm_ OS _workers AS t2
ON t2.task _ address = t1.task _ address
Cross join sys. dm_ OS _sys_info AS t3
WHERE t1.scheduler _ id IS NOT NULL

-- Returns information about sessions other than sleeping sessions. This allows you to monitor thread performance through WINDOWS Performance Monitor.
SELECT STasks. session_id, SThreads. OS _thread_id
FROM sys. dm_ OS _tasks AS STasks
Inner join sys. dm_ OS _threads AS SThreads
ON STasks. worker_address = SThreads. worker_address
WHERE STasks. session_id IS NOT NULL
Order by STasks. session_id



-- Query the running and execution time of threads not started by SQL Server
SELECT * FROM sys. dm_ OS _threads WHERE started_by_sqlservr = 0

-- Query whether an active DAC connection exists. If it is null, it does not exist.
SELECT
T2.session _ id
FROM
Sys. tcp_endpoints AS T1 join sys. dm_exec_sessions AS T2
ON
T1.endpoint _ id = T2.endpoint _ id
WHERE
T1.name = 'dicated Admin connection'