6

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

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

enter image description here

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

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

enter image description here

  1. 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
            )
    
  • 1
    What is the max degree of parallelism set to ? I suspect it is 0 (default) which means use all threads.you can use my script from dba.stackexchange.com/a/36578/8783 to calculate best value for your server instance. – Kin Shah Sep 8 '15 at 12:27
  • 1
    you may want to read worker thread starvation which will help you understand why I recommend to change the MAX DOP away from default value. – Kin Shah Sep 8 '15 at 14:13
  • Thanks again Kin,This really helps me .My problem is there a way to know how many threads are available can serve requests currently – TheGameiswar Sep 8 '15 at 14:15
2
  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'
  1. 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 
    
  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.

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

No that is not the correct query.

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

  2. 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'
    
  • Hi Shanky,thanks for answer.few questions 1.When a thread is suspended for any background activity,will sql server still process it using threads from schedulers .how to know how many threads are available currently to serve requests – TheGameiswar Sep 8 '15 at 14:11
  • No if thread is suspended it will wait untill it gets resource on which it is waiting. To get available worker threads you can look for column current_workers_count from dmv sys.dm_os_schedulers. Read the online documentation please – Shanky Sep 8 '15 at 14:23
0

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.