Excelente post, vai entrar para a caixa de ferramentas.
Como consultar informações sobre lock usando dynamic management views.
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx.
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
Vou traduzir aqui algumas informações do post:
DMVs envolvidas:
- sys.dm_tran_locks
Bloqueios mantidos em todos os recursos para todas as transações. View “base”; mantém informações sobre recursos bloqueados
.- sys.partitions
Informações sobre partições no banco de dados. Podemos fazer join com a view sys.dm_tran_locks usando o campo hobt_id apenas para valores de “resource_type” iguais a ‘HOBT’, ‘Page’, ‘RID’ e ‘Key’. Necessário para obter o valor de “object_id” da tabela bloqueada
.- sys.objects
Informações sobre objetos do banco de dados. Utilizada para obter o nome da objeto bloqueado(normalmente uma tabela)
.- sys.dm_exec_sessions
Informações sobre as sessões conectadas ao SQL server. Usamos para obter informações sobre o login que mantem o bloqueio
.- sys.dm_tran_session_transactions
Informações para as transações de uma sessão
.- sys.dm_tran_active_transactions
Informações sobre todas transações ativas
.- sys.dm_exec_connections
Dados sobre todas conexões ao SQL Server
.- sys.dm_exec_sql_text
Retorna o comando SQL associado à sessão
.Ao filtrar os resultados desta consulta (através da clausula WHERE),obtemos respostas para perguntas como:
- Quais comandos estão causando locks?
- Qual usuário está causando bloqueios?
- Quais tabelas estão bloqueadas?
- Que tipos de locks estão sendo mantidos?
- Etc…
Muito bom!!!