SQL Server thread status

Our SQL is configured to use max of 704 threads,some times we get alerts stating only 10 threads are left,so i am not able to understand whether SQL keeps the thread open ,since again creating a new thread is expensive.

so my question is

-
How to know whether a thread is available for new request or currently busy with other requests.

-
I am trying to link task_address from sys.dm_exec_requests with sys.dm_os_tasks and worker_address like below

select * from sys.dm_exec_requests ec
join
sys.dm_os_tasks tsk
on tsk.task_state=ec.task_address
join
sys.dm_os_workers wrk
on wrk.worker_address=tsk.worker_address

I don't see any output,does this mean i can assume all threads are free

• Currently below is the state of my worker threads,What does this suspended mean ?.I could see count more than 500 with suspended status.I don't see any blockings

4.Can i use below query to find that i have worker thread starvation

select status from sys.dm_Exec_requests

if status is pending,can i assume SQL is waiting for new worker threads

• Currently I see one session,is having more than 250 rows in sysprocesses,when I query sys.dm_os_Waiting_tasks,I could see more than 2186 rows and 90% of them are for same session.So my question here is how can a query span these many threads

-
Will I get available worker count using below query,is this correct ?

select (
        select max_workers_count
        from sys.dm_os_sys_info
        ) - (
        select sum(active_workers_count)
        from sys.dm_os_Schedulers
        )

• How to know whether a thread is available for new request or currently busy with other requests.

You can use DMV sys.dm_os_schedulers to get this information. The column you have to refer is work_queue_count. As per BOL it means


Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.

You can use below query to check number of waiting taks for all online schedulers

select work_queue_count from sys.dm_os_scheduler where status='Visible online'

-
I am not sure what you are trying to achieve but below is what I got from this blog

select wt.session_id, 
  ot.task_state, 
  wt.wait_type, 
  wt.wait_duration_ms, 
  wt.blocking_session_id, 
  wt.resource_description, 
  es.[host_name], 
  es.[program_name] 
  FROM  sys.dm_os_waiting_tasks  wt  
  INNER  JOIN sys.dm_os_tasks ot 
  ON ot.task_address = wt.waiting_task_address 
  INNER JOIN sys.dm_exec_sessions es 
  ON es.session_id = wt.session_id 
  WHERE es.is_user_process =  1

• I could see count more than 500 with suspended status.I dont see any blockings

A suspended is thread which is waiting for some resource and is currently not active. The wait can be I/O, network etc.. See the blog I have shared for more details.

• Can i use below query to find that i have worker thread starvation

No that is not the correct query.

-
Stop using sys.sysprocesses is legacy view and MS does not recommends it to use. Instead use sys.dm_exec_requests

-
You can get worker thread count for each scheduler using DMV sys.dm_os_scheduler

select current_workers_count from sys.dm_os_scheduler where status='visible online'

StackExchange Database Administrators Q#114455, answer score: 2