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'