SQL Server thread status

  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'
    

Tags:

Sql Server