Objetivo
Este documento descreve o procedimento para realizar analise de Locks(bloqueios) no ambiente SQL Server e também matar esses processode bloqueios.
Como Identificar Locks no SQL Server
O SGDB Microsoft SQL Server é capaz de atender a solicitações de um grande número de usuários de forma simultânea. Quando o SQL Server está atendendo a solicitações de muitos clientes, há uma grande possibilidade de que ocorram conflitos porque processos diferentes solicitam acesso aos mesmos recursos ao mesmo tempo.
Um conflito no qual um processo está esperando que outro libere um recurso é chamado de bloqueio ou lock.
Embora no SQL Server um processo bloqueado geralmente se resolva quando o primeiro processo libera o recurso, há momentos em que um processo mantém um bloqueio de transação e não o libera.
Neste artigo, aprenderemos diferentes técnicas para solucionar problemas e resolver bloqueios no SQL Server.
Primeiros Passos
Para resolver um processo bloqueado, primeiro precisamos determinar qual processo é o processo de bloqueio e, em seguida, se possível, encerrar o processo de bloqueio. Existem muitas maneiras diferentes no SQL Server de identificar bloqueios e processos.
Monitor de Atividade(Activity Monitor)
O Activity Monitor é uma ferramenta do SQL Server Management Studio que oferece uma visão das conexões atuais no SQL Server. Você pode usar o Activity Monitor para visualizar informações sobre os processos e bloqueios atuais mantidos nos recursos do SQL Server. Para abrir o Activity Monitor no SQL Server Management Studio, clique com o botão direito do mouse no nome da instância do SQL Server no Object Explorer e selecione Activity Monitor(Monitor de Atividade para o SSMS em Português):
Para encontrar o processo bloqueado com o Activity Monitor, primeiro clique em Processos no Monitor de atividades para abrir a página Informações do processo:
Em seguida, localize o processo que está aguardando e role até a coluna Bloqueado por e observe a ID do processo nessa coluna. Encontre o ID do processo na página Informações do processo
Se você localizou o processo bloqueado clique com o botão direito e escolha Kill Process ou Eliminar Processo para SSMS em Português:
Performance Monitor
O Performance Monitor visualiza as estatísticas atuais ou e possível criar um log ou alerta para monitorar bloqueios. Por exemplo, você pode monitorar as estatísticas de Tempo Médio de Espera , Número de deadlocks e Tempo Limite de Bloqueio para determinar se há um problema com a contenção de recursos no SQL Server. No entanto, você precisará de informações adicionais para determinar a causa exata do problema. Siga as etapas abaixo para monitorar o SQLServer via Performance Monitor.
No menu Iniciar clique em Executar, digite perfmon na caixa de diálogo Executar e clique em OK para iniciar o Monitor de Desempenho.
Clique com o botão direito em qualquer lugar da tela e escolha Adicionar contadores.
Role para baixo para localizar os contadores de bloqueio do SQL Server, adicione esses três contadores e clique em OK.
- Average Wait Time(Tempo Médio de Espera)
- Number of deadlocks/sec(Número de deadlocks por segundos)
- Lock Timeouts/sec(Tempo limite de bloqueio por segundo)
Clique em OK e a partir desse ponto começamos a monitorar essas atividades no SQL Server
DMVs (Visualizações de gerenciamento dinâmico)
sys.dm_exec_requests
Você pode usar a exibição de gerenciamento dinâmico sys.dm_exec_requests para obter informações detalhadas sobre as solicitações em execução no SQL Server. A visão de gerenciamento dinâmico inclui informações detalhadas sobre a consulta e plano de consulta, status da solicitação e informações sobre a quantidade de tempo que está em execução. As colunas que você provavelmente usará ao solucionar um bloqueio ou deadlock são as seguintes:
Blocking_session_id – O SPID da sessão de bloqueio.
wait_type – Tipo de espera.
wait_time – Tempo de espera da solicitação (em milissegundos).
last_wait_type – Se uma espera terminou, seu tipo é listado aqui.
wait_resource – Nome do recurso que a solicitação está esperando.
transaction_isolation_level – nível de isolamento da transação.
lock_timeout – Período de tempo que um bloqueio pode existir antes de expirar
Para visualizar o processo bloqueado, execute a seguinte consulta:
USE [master] GO SELECT session_id ,blocking_session_id ,wait_time ,wait_type ,last_wait_type ,wait_resource ,transaction_isolation_level ,lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 GO
sys.dm_tran_locks
Você pode ver informações sobre os bloqueios atuais e os processos que os bloqueiam usando a visão de gerenciamento dinâmico sys.dm_tran_locks. Esta coluna possui um de três valores: GRANT, WAIT ou CONVERT. O valor de CONVERT significa que uma solicitação foi concedida ao solicitante, mas está aguardando a atualização para a solicitação inicial a ser concedida. Para localizar informações sobre todos os bloqueios com um status de solicitação de CONVERT, execute o seguinte:
USE [master] GO SELECT * from sys.dm_tran_locks WHERE request_status = 'CONVERT' GO
A coluna request_session_id contém o ID do processo para o processo. Para visualizar o bloqueio no banco de dados específico, execute a seguinte consulta que une sys.dm_tran_locks com sys.partitions:
USE [master] GO SELECT tl.resource_type ,tl.resource_associated_entity_id ,OBJECT_NAME(p.object_id) AS object_name ,tl.request_status ,tl.request_mode ,tl.request_session_id ,tl.resource_description FROM sys.dm_tran_locks tl LEFT JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id WHERE tl.resource_database_id = DB_ID() GO
sys.dm_os_waiting_tasks
A exibição de gerenciamento dinâmico sys.dm_os_waiting_tasks relata informações sobre os processos de bloqueio e bloqueio. O processo bloqueado é listado na coluna session_id. O bloqueio está listado na coluna blocking_session_id.
Execute o seguinte para visualizar as estatísticas de espera para todos os processos de bloqueio no SQL Server:
USE [master] GO SELECT w.session_id ,w.wait_duration_ms ,w.wait_type ,w.blocking_session_id ,w.resource_description ,s.program_name ,t.text ,t.dbid ,s.cpu_time ,s.memory_usage FROM sys.dm_os_waiting_tasks w INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t WHERE s.is_user_process = 1 GO
Esse detalhe é bom para uma visão geral ou para se ter uma ideia rápida dos tipos de esperas que ocorrem, mas a maioria dos diagnósticos e ajustes reais ocorrerão em nível de instrução.
SQL Server Profiler
Use a categoria de evento SQL Server Profiler Locks para criar um rastreamento de eventos relacionados a bloqueios e impasses. Você pode escolher uma ou mais dessas classes de eventos:
Na Guia General na opção “Use the Template” escolha TSQL_Locks
Na Guia Events Selection você pode definir o que o Trace do SQL Server Profiler ira rastrear.
sp_who ou sp_who2
Ambos sp_who ou sp_who2 retornam informações sobre todas as sessões estabelecidas atualmente no banco de dados e são indicadas como spid’s. Ambos os procedimentos de armazenamento aceitam parâmetros. O preto coluna de sp_who e blkby coluna de sp_who2 contém a SPID para o processo de bloqueio. A execução de sp_who e sp_who2 é fácil, por exemplo, a seguinte chamada desses procedimentos retorna todos os processos que estão atualmente ativos no SQL Server:
USE master; GO EXEC sp_who 'active'; GO EXEC sp_who2 'active'; GO
Use a instrução KILL para encerrar o processo bloqueado
Use a instrução KILL para visualizar o status de um processo ou eliminar o processo. A instrução KILL tem a sintaxe: KILL spid | UOW [COM STATUSONLY]
USE master; GO KILL spid | UOW [WITH STATUSONLY] GO
Você deve passar por um spid ou, se o processo pertencer a uma transação de Coordenação de Transações Distribuídas (DTC), você deve fornecer uma Unidade de Trabalho (UOW). Você deve ser membro de sysadmin ou processadmin para encerrar um processo. Você pode obter o spid da sessão atual executando @@ spid. Você pode obter o spid para as sessões associadas a um logon executando sp_who2. Se você não especificar um login, sp_who2 retornará informações para todas as conexões atuais.
Conclusão
Se você não pode eliminar um processo de bloqueio, pode ser necessário reiniciar o serviço do SQL Server. Isso fará com que todas as conexões atuais sejam fechadas, portanto, você deve evitar reiniciar o serviço, se possível.
