tanitelle's questions

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.

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?

Consultar o sys.dm_tran_locksDMV nos mostra quais sessões (SPIDs) estão mantendo bloqueios em recursos como tabela, página e linha.

Para cada bloqueio adquirido, existe alguma maneira de determinar qual instrução SQL (excluir, inserir, atualizar ou selecionar) causou esse bloqueio?

Eu sei que a most_recent_query_handlecoluna do sys.dm_exec_connectionsDMV nos dá o texto da última consulta executada, mas várias vezes outras consultas foram executadas antes na mesma sessão (SPID) e ainda estão mantendo bloqueios.

Já utilizo o sp_whoisactiveprocedimento (de Adam Machanic) e ele mostra apenas a consulta que está no buffer de entrada no momento (pense DBCC INPUTBUFFER @spid), que nem sempre (e no meu caso geralmente nunca) é a consulta que adquiriu o bloqueio.

Por exemplo:

1. abrir transação/sessão
2. exec uma instrução (que mantém um bloqueio em um recurso)
3. exec outra instrução na mesma sessão
4. abra outra transação/sessão e tente modificar o recurso bloqueado na etapa 2.

O sp_whoisactiveprocedimento apontará a instrução na etapa 3, que não é a responsável pelo bloqueio e, portanto, não é útil.

Essa pergunta surgiu ao fazer uma análise usando o recurso Blocked Process Reports , para encontrar a causa raiz dos cenários de bloqueio na produção. Cada transação executa várias consultas e, na maioria das vezes, a última (que é mostrada no buffer de entrada no BPR) raramente é a que mantém o bloqueio.

Eu tenho uma pergunta de acompanhamento: Estrutura para identificar efetivamente as consultas de bloqueio

Descobri que a consulta a seguir mostra quantas variáveis ​​de contexto de sessão foram definidas:

SELECT cache_address
      ,name
      ,pages_kb
      ,pages_in_use_kb
      ,entries_count
      ,entries_in_use_count
FROM   sys.dm_os_memory_cache_counters
WHERE  TYPE = 'CACHESTORE_SESSION_CONTEXT';

Mas agora eu gostaria de saber quais pares de valores-chave SESSION_CONTEXT()foram definidos - se houver.

Depois de fazer uma extensa pesquisa em sites da microsoft não encontrei nada relacionado.

Desde já, obrigado.