SQL Server thread status

Question

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

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

2. 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

3. 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

5. 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

6. 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
           )
   

Answer 1

1. 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'

2. 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 
   

3. 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.

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

No that is not the correct query.

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

6. 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'
   

Answer 2

I did some testing and found few clarifications to my question.Adding them here.

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

SQL server will allocate threads based on this formula and the same can be found in sys.dm_os_sys_info as well,SQL by default starts with initial threads and will keep increasing the threads upto max threads limit based on load.Point to note is SQL wont kill the threads once created because it is expensive to create them again.

In my case we used to get alerts stating only 1o threads are available,but i am not able to figure out are there any threads available to serve requests.

So did small testing using Ostress tool based on this blog and found that available threads can be found at any point of time using below query

select (select max_workers_count from sys.dm_os_sys_info) as 'TotalThreads',sum(active_Workers_count) as 'Currentthreads',(select max_workers_count from sys.dm_os_sys_info)-sum(active_Workers_count) as 'Availablethreads',sum(runnable_tasks_count) as 'WorkersWaitingfor_cpu',sum(work_queue_count) as 'Request_Waiting_for_threads' 
from  sys.dm_os_Schedulers where status='VISIBLE ONLINE'

2.I am trying to link task_address from sys.dm_exec_requests with sys.dm_os_tasks and worker adress like below

Sorry if my question is not clear,but i wanted to check whether a query is running in parallel.

Any parallel query will have an entry in sys.dm_os_tasks Plus sys.sysprocesses,so to run which queries are running parallely,we can use below dmv.

select
 er.session_id,
 er.status,
 er.command,
 er.blocking_session_id,
 er.wait_type,
 ot.exec_context_id,
 ot.task_state,
 st.text
from
 sys.dm_exec_requests er
 join sys.dm_os_tasks ot on (er.session_id = ot.session_id)
 cross apply sys.dm_exec_sql_text(er.sql_handle) st
where er.session_id in
 (select session_id
 from sys.dm_os_tasks
group by session_id
having count(exec_context_id)>1)

3.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 dont see any blockings

A thread can be suspended due to many reasons,i checked wait type and all spids are waiting for CXpacket

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

To know if you are suffering from worker thread starvation,use below queries.

select sum(work_queue_count) from sys.dm_os_schedulers
select * from sys.dm_os_tasks

select * from sys.dm_os_waiting_tasks where wait_type='threadpool'

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

As per my understanding ,SQL when using parallelism spawns many threads if they are available and number of threads SQL spans is not limited to number of CPU cores,that is what happened in my case ,so we will try to check why this query cost is considered as high by SQL and will test this.

And thanks for the resources online,i had a misconception that when a thread is blocked with async i/O,i thought that SQL will try to offload this work to OS thread ,but this is not the case