Início / dba / Perguntas / 200099
Accepted
tanitelle
Asked: 2018-03-14 05:19:48 +0800 CST

Estrutura para identificar efetivamente as consultas de bloqueio

  • 772

Preciso de uma rotina para identificar efetivamente quais consultas causaram o bloqueio. Isso está relacionado à minha pergunta anterior Como encontrar a consulta que ainda está mantendo um bloqueio? .

Eu sei que existe um monte de material online sobre isso, mas todos eles são baseados na premissa de que a última instrução SQL em uma sessão ativa é provavelmente aquela que adquiriu o bloqueio (daí gerando o bloqueio), o que nem sempre é true (no meu caso, nunca).

Configurei blocking-process-thresholdpara 30 segundos e comecei a analisar os Relatórios de Processo de Bloqueio (BPR).
Esses relatórios são acionados sempre que ocorre um bloqueio, quando o limite é atingido.
Ele contém informações sobre o spid bloqueado e o spid de bloqueio.

Exemplo de BPR

Muitas vezes, o spid de bloqueio executa algumas instruções após a que adquiriu e está mantendo o bloqueio em um recurso (tabela, página ou linha): então, apesar do conteúdo do relatório, continuo sem saber qual consulta exatamente causou esse bloqueio.

Normalmente, os DMVs do SQL Server mostram apenas o último texto SQL para cada session_id, e os DMVs relacionados a bloqueios ativos (como sys.dm_tran_locks) também não abordam esse problema.

Ajustar as consultas bloqueadas não é a melhor abordagem aqui: nossa aplicação é toda baseada em SQL dinâmico embutido no código do cliente, não usamos procedimentos armazenados e com base nos bloqueios que vi até agora, todas as consultas bloqueadas foram indexadas corretamente e escrito.

Acho que uma opção para resolver isso seria coletar consultas candidatas , que poderiam ter gerado um bloqueio e, em seguida, pesquisar essas informações usando timestamp e spid reunidos no BPR. Você concorda? Em caso afirmativo, você pode apontar uma maneira de fazer isso com o mínimo de sobrecarga possível usando xEvents?

sql-server locking

2 respostas

  1. Best Answer

    Sugiro procurar sessões de longa duração, usando uma sessão XEvents.

    O problema que você está descrevendo parece que você tem um código de cliente que está executando o processamento de linha por linha ( RBAR ) em vez de usar abordagens baseadas em conjuntos eficientes.

    Aplicativos cliente mal projetados podem fazer algo assim:

    1. Conecte-se ao SQL Server para obter uma lista de itens que precisam de processamento. O resultado da consulta é mantido aberto até que todas as linhas tenham sido processadas.
    2. executar algum processo de longa duração em cada linha.
    3. Feche a consulta.

    O que deve acontecer é:

    1. Conecte-se ao SQL Server
    2. Execute uma consulta, armazenando em cache os resultados da consulta localmente e fechando a conexão.
    3. Execute o processo de longa duração em cada linha sem manter a consulta original aberta, evitando assim o bloqueio.

    Uma solução alternativa para o SQL Server pode ser implementada usando o controle de versão de linha de isolamento de instantâneo. Consulte este documento Technet para obter detalhes. Essencialmente, o versionamento de linhas permite que os escritores não bloqueiem os leitores, reduzindo bastante o bloqueio.

    • 6

  2. Você pode obter informações de bloqueio dos DMVs:

    select  getdate() Run_Date,
        r.session_id,
        r.blocking_session_id,
        st.text SQL_Text
from    sys.dm_exec_requests r
        cross apply sys.dm_exec_sql_text(r.sql_handle) st
where   r.blocking_session_id <> 0
        or r.session_id in( select r2.blocking_session_id
                            from sys.dm_exec_requests r2
                            where r2.blocking_session_id <> 0)

    Você pode executá-lo dentro de um loop com um tempo de espera de sua escolha e gerar os resultados em uma tabela.

    Ele não capturará todos os blocos que seu sistema encontrar, mas se você estiver encontrando muitos bloqueios, ele deverá capturar o suficiente para você iniciar a solução de problemas.

    • 0

relate perguntas