SQL Server: Monitoramento de Locks no SQL Server 🔍

📌 Introdução

Esta query é utilizada para identificar locks (bloqueios) que ocorrem dentro de um banco de dados SQL Server. Locks podem impedir que transações sejam concluídas, causando lentidão e impactando o desempenho do sistema. O objetivo desta query é listar os objetos bloqueados, os tipos de bloqueios, as sessões envolvidas e outras informações úteis para análise.

🛠️ Estrutura da Query

SELECT
        L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    
  sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   
  resource_database_id = db_id()
ORDER BY 
  L.request_session_id  

🔍 Explicação dos Campos

• SPID (request_session_id): ID da sessão que está segurando o lock.

• DatabaseName (resource_database_id): Nome do banco de dados onde o lock está ocorrendo.

• LockedObjectName (O.Name): Nome do objeto bloqueado (tabela, índice, etc.).

• LockedObjectId (P.object_id): ID do objeto bloqueado.

• LockedResource (resource_type): Tipo de recurso bloqueado (ex.: KEY, PAGE, OBJECT).

• LockType (request_mode): Tipo de bloqueio aplicado (ex.: Shared, Exclusive, Update).

• SqlStatementText (ST.text): Última instrução SQL executada pela sessão que está segurando o lock.

• LoginName (ES.login_name): Nome do usuário que iniciou a sessão bloqueadora.

• HostName (ES.host_name): Nome da máquina que originou a conexão.

• IsUserTransaction (TST.is_user_transaction): Indica se a transação é do usuário ou do sistema.

• TransactionName (AT.name): Nome da transação ativa.

• AuthenticationMethod (CN.auth_scheme): Método de autenticação utilizado pela conexão.

📌 Como Utilizar

1. Execute a query no SQL Server Management Studio (SSMS).

2. Analise os resultados para identificar quais sessões estão causando bloqueios.

3. Se necessário, finalize sessões problemáticas com o comando KILL <SPID>.

4. Investigue a causa do bloqueio e avalie a necessidade de otimização de índices ou reestruturação de transações.

⚠️ Cuidados ao Utilizar

• Evite matar sessões sem avaliar o impacto no sistema.

• Locks podem ser normais em operações concorrentes; foque em resolver os bloqueios persistentes.

• Ajuste suas transações para minimizar a duração dos bloqueios.

🎯 Conclusão

Esta query é uma ferramenta essencial para identificar e solucionar problemas de bloqueios no SQL Server. Com ela, você pode obter insights sobre sessões, objetos bloqueados e usuários envolvidos, permitindo um diagnóstico preciso e ações corretivas eficazes. 🚀