SQL Server travando: como achar a query que bloqueia as outras

Comece pelo topo da cadeia de bloqueio, não pela sessão que reclamou. Quando o SQL Server "trava", quase sempre existem dezenas de sessões esperando e apenas uma segurando o lock que ninguém enxerga — normalmente uma transação aberta há minutos, sem estar executando nada. Matar as sessões que esperam não resolve; encontrar o bloqueador raiz resolve.

O sintoma é conhecido: a aplicação fica lenta, o gráfico de CPU está tranquilo, o disco não está saturado, e reiniciar o serviço "conserta". Nada disso aponta para hardware. Aponta para concorrência.

Bloqueio não é o mesmo que lentidão

O SQL Server usa locks para garantir isolamento. Uma transação que alterou uma linha mantém um lock exclusivo nela até dar COMMIT ou ROLLBACK. Enquanto isso, quem precisa ler ou alterar a mesma linha espera. Isso é correto — é o mecanismo funcionando.

O problema aparece quando a espera deixa de ser instantânea, e há três causas típicas para isso:

• Transação longa. A aplicação abre a transação, faz uma chamada externa, processa em memória, e só então dá COMMIT. O lock fica preso o tempo todo.
• Transação esquecida. Conexões com transações implícitas (IMPLICIT_TRANSACTIONS ON) abrem transação sozinhas em cada comando e dependem de um COMMIT explícito que o código nunca envia.
• Varredura desnecessária. Sem índice adequado, um UPDATE que deveria tocar uma linha faz scan e trava a tabela inteira no caminho.

Distinguir qual dos três está em jogo muda completamente o que precisa ser corrigido.

Encontrando o bloqueador raiz

sys.dm_exec_requests mostra quem está esperando e por quem, via blocking_session_id. A informação que costuma faltar é o texto da query e, principalmente, o texto de quem bloqueia:

SELECT    r.session_id,    r.blocking_session_id,    r.wait_type,    r.wait_time / 1000.0        AS espera_s,    r.wait_resource,    t.text                      AS query_em_execucaoFROM sys.dm_exec_requests AS rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.blocking_session_id <> 0;

Essa consulta lista os bloqueados. Para chegar ao topo da cadeia, siga blocking_session_id até encontrar uma sessão que bloqueia mas não é bloqueada. E aqui está a parte que engana: o bloqueador raiz costuma não aparecer em sys.dm_exec_requests, porque não está executando nada. Ele tem uma transação aberta e está ocioso.

SELECT    s.session_id,    s.login_name,    s.host_name,    s.program_name,    s.status,    s.last_request_end_time,    t.open_transaction_count,    c.most_recent_sql_handleFROM sys.dm_exec_sessions       AS sJOIN sys.dm_tran_session_transactions AS t ON t.session_id = s.session_idLEFT JOIN sys.dm_exec_connections     AS c ON c.session_id = s.session_idWHERE s.session_id <> @@SPID;

Uma sessão com status = 'sleeping', open_transaction_count > 0 e last_request_end_time de vários minutos atrás é exatamente o padrão de transação esquecida. most_recent_sql_handle passado por sys.dm_exec_sql_text mostra o último comando que ela executou — quase sempre suficiente para identificar o ponto do código responsável.

sys.dm_os_waiting_tasks complementa o quadro quando a espera acontece em nível de tarefa, e os tipos de espera que começam com LCK_M_ confirmam que se trata de lock, não de I/O ou de CPU.

O que não resolve

WITH (NOLOCK) não é correção de bloqueio. Ele evita a espera porque lê dados sujos: linhas que ainda podem sofrer rollback. Em varreduras que acompanham a ordem de alocação, também pode retornar a mesma linha duas vezes ou pular linhas quando ocorre movimentação de páginas durante a leitura. Trocar consistência por velocidade é uma decisão de negócio, não um ajuste técnico neutro — e raramente é a decisão que alguém tomaria conscientemente para um relatório financeiro.

READ_COMMITTED_SNAPSHOT resolve metade do problema. Ativado, leitores passam a enxergar a versão anterior da linha em vez de esperar, o que elimina o bloqueio entre leitura e escrita. O bloqueio entre duas escritas continua igual, porque continua sendo necessário. O custo é o versionamento em tempdb, que precisa ser dimensionado antes de ligar a opção em produção.

Escalar hardware não resolve. Uma transação parada esperando COMMIT não anda mais rápido com mais CPU.

Escalonamento de lock

Quando uma única instrução acumula muitos locks de linha em um mesmo objeto, o SQL Server pode converter tudo em um lock de tabela para economizar memória. O efeito prático é abrupto: uma operação que incomodava algumas linhas passa a travar a tabela inteira.

O gatilho aparece em cargas que fazem DELETE ou UPDATE em lote sem filtro seletivo. A saída costuma ser dividir o lote em partes menores, cada uma em sua própria transação, de forma que nenhuma instrução isolada acumule locks suficientes para escalar. Índice adequado no critério de filtro ajuda no mesmo sentido: menos linhas tocadas, menos locks.

Bloqueio recorrente pede captura contínua

Investigar durante o incidente funciona, mas depende de alguém estar conectado no momento certo. Para bloqueio que se repete, vale configurar o blocked process report e capturá-lo com Extended Events, definindo um limiar em segundos a partir do qual o evento é gravado. O relatório traz as duas pontas — bloqueado e bloqueador — com o XML do plano e do comando.

Deadlocks são um caso à parte: não são bloqueio prolongado, são dois processos esperando um ao outro, e o SQL Server escolhe uma vítima e faz rollback. A sessão system_health, que já vem ativa por padrão, guarda o grafo de deadlock recente — não é preciso instrumentar nada para começar a investigar.

O que olhar depois

Bloqueio raramente é um evento isolado. Ele costuma aparecer junto de plano de execução que regrediu, índice que deixou de ser usado ou crescimento de tabela que mudou a seletividade de um filtro. Vale acompanhar essas evidências no tempo em vez de reagir a cada ocorrência — o que passa por escolher as métricas que realmente antecipam um incidente, e não as que só confirmam o que já aconteceu.

Próximo passo

Se o ambiente já vive sob bloqueios recorrentes e a resposta atual é reiniciar ou matar sessão, o gargalo é de diagnóstico, não de capacidade.

A H1 Data faz um diagnóstico gratuito do ambiente SQL Server que inclui a leitura das cadeias de bloqueio, dos padrões de transação da aplicação e das esperas dominantes, com priorização do que muda o quadro primeiro.