A consulta a seguir foi executada no SQL Server 2008 R2 e levou mais de 1 hora para ser executada:
select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id
Produziu 0 linhas retornadas. Sei disso porque restaurei o banco de dados no momento em que a consulta começou a ser executada e a executei novamente. Quando fiz isso, a consulta correu muito rápido (menos de 1 segundo).
Enquanto essa consulta estava em execução por mais de uma hora, sp_WhoIsActive foi iniciado a cada 5 minutos por meio de um trabalho do SQL Agent e registrou os detalhes em uma tabela. A imagem abaixo, que você deve baixar e visualizar ampliada , mostra o que estava sendo executado durante esse período.
A consulta de execução longa é destacada em verde. A coluna wait_info é nula e o sleep_status está sempre dormindo.
Alguma ideia de por que essa consulta demorou tanto para ser executada e/ou alguma ideia sobre o que posso fazer para solucionar o problema ainda mais?
Concordo com @Ian na maior parte. Deixe-me expandir, no entanto.
Provavelmente é o IIS mantendo a conexão aberta, mas é por design devido ao pool de conexões, que é quase sempre usado por padrão atualmente.
O outro ponto é que a consulta não foi executada por mais de uma hora. Preste muita atenção à incrível documentação que Adam inclui em sp_WhoIsActive. Execute-o com @help =1. No atributo de tempo que você está usando para medir o tempo de execução, Adam escreve o seguinte:
E para o atributo sql_text, Adam escreve:
O que realmente está acontecendo aqui é que a consulta é concluída muito rapidamente e a conexão é liberada de volta para o pool (no estado 'dormindo') para ser apanhada quando for solicitada novamente. Os resultados em sua tabela refletem que a última conexão que executou a consulta está simplesmente sem uso por um longo período de tempo. Então, nada com que se preocupar, tudo está funcionando conforme planejado.
No entanto, você pode modificar seu trabalho de agente para executar sp_WhoIsActive com @show_sleeping_spids = 1, que mostrará apenas spids adormecidos se eles tiverem uma transação ativa, para que você não veja mais essas conexões em pool.
ATUALIZAR:
Ao olhar para a captura de tela novamente, vejo que você realmente tem uma transação aberta mantida nesse spid. Isso realmente não afeta minha resposta, exceto o último parágrafo, mas você precisa descobrir por que essa transação está sendo mantida. Você tem transações implícitas ativadas? Você está deixando de confirmar uma transação explícita no mesmo lote?
O status de suspensão não indica que é uma consulta de execução longa, mas sim que a conexão estabelecida para executar essa consulta ainda está aberta. Enquanto essa conexão estiver aberta, ela será capturada e registrada em sua tabela. Parece que é mais um problema ou o IIS não está fechando a conexão com o banco de dados.