O SQL Server tem alguma maneira simples de buscar métricas sobre quantos (e possivelmente qual tipo) de bloqueios uma determinada consulta obteve durante sua execução?
Justificativa...
Estou no processo de reescrever um procedimento armazenado bastante desagradável que faz uso intenso de cursores. Uma solução baseada em conjunto é possível e, como resultado, tenho três versões dela: a original, uma versão baseada em conjunto com vars de tabela e uma versão baseada em conjunto puramente baseada em CTE. Estou obtendo boas métricas para a solução baseada em CTE, mas devido ao tamanho e à complexidade da consulta criada pela versão CTE, tenho receio de lançá-la até que eu tenha feito o máximo de diligência possível sobre ela.
Agora percebo que os bloqueios que o SQL remove são aqueles que ele acha que precisa remover em um determinado momento e, em um servidor de produção carregado, pode se comportar de maneira diferente, mas espero que isso me dê uma sensação de como vai se comportar.
Atualmente, estou usando o profiler e mantendo as transações abertas para que eu possa executar sp_lock, mas queria saber se estou perdendo um truque no SQL Server 2008.
Como alternativa, estou tentando me convencer de que o criador de perfil que retorna um grande número de leituras ao inserir em uma variável de tabela está bem (para um determinado valor de ok), mas não me convenci. A solução CTE tem metade das leituras da versão table-var, e a alta contagem de leituras da versão table-var parece ser puramente devido às inserções. Em termos de duração, tanto o CTE quanto o table var um são aproximadamente os mesmos, dando tempos de execução aproximadamente 150-200% mais rápidos do que a abordagem baseada em cursor.
Eventos estendidos com destino de histograma, destino de contador de eventos ou destino de bucketizer. Você pode começar com o exemplo Como: Localizar os objetos que têm mais bloqueios feitos neles no MSDN, que oferece quase exatamente o que você deseja. Você pode modificar o exemplo para obter os tipos de bloqueio (o exemplo 'como está' filtra os bloqueios S e U) e pode refiná-lo para filtrar por sessão etc etc.
Dito isto, devo salientar que abordar uma investigação de desempenho a partir da contagem dos bloqueios adquiridos é altamente incomum. O foco geralmente está nas estatísticas de espera, conforme descrito na metodologia Waits and Queues . Veja, por exemplo , Capturando estatísticas de espera para uma única operação para saber como usar, novamente, Eventos Estendidos para capturar as informações relevantes para uma consulta específica.
Por outro lado, se você estiver preocupado com os bloqueios do ponto de vista da simultaneidade (os novos CTEs criarão mais contenção devido ao bloqueio?) Então, eu recomendaria investigar a implantação do isolamento de instantâneo .
Pegue o sid da consulta quando ela estiver em execução e, em seguida, conecte-o a sys.dm_tran_locks .