Status do encadeamento do SQL Server

1. Como saber se um thread está disponível para uma nova solicitação ou atualmente ocupado com outras solicitações.

Você pode usar DMV sys.dm_os_schedulers para obter essas informações. A coluna que você deve consultar é work_queue_count. De acordo com o BOL, significa

Número de tarefas na fila pendente. Essas tarefas estão esperando que um trabalhador as pegue. Não é anulável.

Você pode usar a consulta abaixo para verificar o número de tarefas em espera para todos os agendadores online

select work_queue_count from sys.dm_os_scheduler where status='Visible online'

2. Não tenho certeza do que você está tentando alcançar, mas abaixo está o que recebi deste 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. Pude ver a contagem de mais de 500 com status suspenso. Não vejo nenhum bloqueio

Um suspenso é um encadeamento que está aguardando algum recurso e não está ativo no momento. A espera pode ser I/O, rede etc. Veja o blog que compartilhei para mais detalhes.

4. Posso usar a consulta abaixo para descobrir que tenho inanição de thread de trabalho

Não, essa não é a consulta correta.

5. Parar de usar sys.sysprocesses é uma exibição herdada e a MS não recomenda seu uso. Em vez disso, usesys.dm_exec_requests

6. Você pode obter a contagem de threads de trabalho para cada agendador usando DMV sys.dm_os_scheduler

      select current_workers_count from sys.dm_os_scheduler where status='visible online'
   

Fiz alguns testes e encontrei poucos esclarecimentos para minha dúvida.Adicionando-os aqui.

1.Como saber se um thread está disponível para uma nova requisição ou atualmente ocupado com outras requisições.

O servidor SQL irá alocar threads com base nesta fórmula e o mesmo pode ser encontrado em sys.dm_os_sys_info também, o SQL por padrão começa com threads iniciais e continuará aumentando os threads até o limite máximo de threads com base na carga. os encadeamentos uma vez criados porque é caro criá-los novamente.

No meu caso, costumávamos receber alertas informando que apenas 10 threads estão disponíveis, mas não consigo descobrir se há threads disponíveis para atender às solicitações.

O mesmo aconteceu com pequenos testes usando a ferramenta Ostress com base neste blog e descobri que os tópicos disponíveis podem ser encontrados a qualquer momento usando a consulta abaixo

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. Estou tentando vincular task_address de sys.dm_exec_requests com sys.dm_os_tasks e endereço de trabalho como abaixo

Desculpe se minha pergunta não está clara, mas queria verificar se uma consulta está sendo executada em paralelo.

Qualquer consulta paralela terá uma entrada em sys.dm_os_tasks Plus sys.sysprocesses, portanto, para executar quais consultas estão sendo executadas paralelamente, podemos usar abaixo 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. Atualmente, abaixo está o estado dos meus threads de trabalho, o que isso significa suspenso?. Pude ver a contagem de mais de 500 com status suspenso.

Um thread pode ser suspenso devido a vários motivos, verifiquei o tipo de espera e todos os spids estão aguardando o CXpacket

4. Posso usar a consulta abaixo para descobrir que tenho inanição de thread de trabalho

Para saber se você está sofrendo de inanição de thread de trabalho, use as consultas abaixo.

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'

Atualmente vejo uma sessão, está tendo mais de 250 linhas em sysprocesses, quando consulto sys.dm_os_Waiting_tasks, pude ver mais de 2186 linhas e 90% delas são para a mesma sessão. Portanto, minha pergunta aqui é como uma consulta pode abranger essas muitos tópicos

Pelo que entendi, o SQL ao usar o paralelismo gera muitos threads se eles estiverem disponíveis e o número de threads que o SQL abrange não é limitado ao número de núcleos da CPU, foi o que aconteceu no meu caso, então tentaremos verificar por que esse custo de consulta é considerado alto pelo SQL e testará isso.

E obrigado pelos recursos online, eu tive um equívoco de que quando um thread é bloqueado com i/O assíncrono, pensei que o SQL tentaria descarregar esse trabalho para o thread do sistema operacional, mas esse não é o caso