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.
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
group by session_id
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
max degree of parallelismset 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.
MAX DOPaway from default value.