SQL Server adora bloqueios.
SQL Server usa o bloqueio “pessimista” por defeito – as suas consultas são naturalmente defensivas. Isto pode levar a problemas de bloqueio que o impedem de escalonar o SQL Server.
Classe de treinamento em vídeo livre – antes de resolver problemas de bloqueio e deadlocks, comece com How to Think Like the Engine. É uma classe de ritmo rápido de 90 minutos que explica como funcionam os internos de índice agrupados e não agrupados, e isso é vital para entender como a indexação afeta o bloqueio. É gratuito – porque acreditamos que todos os profissionais do SQL Server devem começar a sua formação aqui. É realmente importante – bom material.
Ferramentas para diagnosticar a contenção do bloqueio
“Será que eu tenho bloqueios à espera neste momento? Use sp_BlitzFirst® para diagnosticar se as esperas de bloqueio estão atualmente impactando o seu SQL Server.
“Quais tabelas têm os piores problemas de bloqueio?” Execute sp_BlitzIndex® e procure por “Aggressive Index Disorder” para identificar quais tabelas tiveram altas quantidades de esperas de bloqueio (para tabelas e índices atualmente na memória).
“Como faço para entender o Relatório de Processo Bloqueado”? Quando você captura o Relatório de Processos Bloqueados do SQL Server (scripts abaixo), o Visualizador de Relatórios de Processos Bloqueados de Michael J Swart é uma ferramenta gratuita que o ajuda a interpretar os resultados.
“E se minha aplicação não capturar erros de deadlock?” Amit Banerjee da equipe CSS da Microsoft ajuda você a consultar a sessão System Health do SQL Server para detalhes (SQL Server 2008+).
Contadores de Desempenho Relacionados ao Bloqueio
Estes contadores de desempenho podem ser muito úteis para configurar um Alerta de Agente SQL Server baseado em um contador SQL Server para que o SQL Server possa notificá-lo quando o bloqueio ultrapassa os limites que você definiu.
- Estes são listados OBJETO primeiro, depois COUNTER
- SQL Server: General Statistics – Processes Blocked
- SQL Server: Bloqueios – Lock Wait Time (ms)
- ServidorSQL: Locks – Number of Deadlocks/sec
Saiba mais sobre mais contadores perf no SQL Server aqui.
Ler artigos sobre Locking, Isolation Levels, and Deadlocks
Ler uma introdução sobre locks e deadlocks bifásicos de Jeremiah.
Saiba como você pode testar mudanças de código para descobrir quais bloqueios são necessários de Kendra.
Jeremiah ensina-lhe como encontrar e prevenir bloqueios.
Aprenda como implementar bloqueios otimizados no SQL Server a partir do Kendra.
Leia mais sobre os níveis de isolamento no SQL Server – veja nossa página de links e referências abrangentes.
Scripts para usar o Blocked Process Report
O “Blocked Process Report” já existe há muito tempo no SQL Server- e ainda é uma ferramenta útil. Por padrão no SQL Server, o detector de bloqueios acorda a cada 5 segundos para verificar se as suas consultas estão em um “abraço mortal”. Você pode ativar uma opção que pede ao SQL Server para usar este recurso para verificar se há bloqueio a longo prazo, e emitir um relatório.
WARNING: Você só deve configurar o relatório do processo bloqueado para ser emitido para valores de cinco segundos ou superiores. A Microsoft avisa que se você definir isto de 1-4, você pode fazer com que o detector de bloqueios seja executado continuamente e mate seu desempenho.
Para usar o Relatório de Processos Bloqueados, você precisa configurar um rastreamento que procure o evento “Relatório de Processos Bloqueados” em “Erros e Avisos”. Recomendamos o uso de um rastreamento do lado do servidor – e, claro, somente rodando isto quando você precisar.
Deseja ver uma demonstração disto? Veja uma aqui.
Scripts nesta seção são exemplos de maneiras comuns para fazer estas tarefas – você deve testar e revisar de acordo com o seu ambiente, e executar por sua própria conta e risco.
Passo 1: Ative o relatório de processo bloqueado. Isto irá procurar por qualquer bloqueio que demore 20 segundos ou mais.
|
1
2
3
4
5
6
6
7
8
9
10
11
12
13
14
15
|
–Certifique-se de que não tem nenhuma alteração pendente
SELECT *
FROM sys.configurações
WHERE value <> value_in_use;
GO
exec sp_configure ‘show advanced options’, 1;
GO
RECONFIGURA
GO
exec sp_configure ‘blocked process threshold (s)’, 20;
GO
RECONFIGURA
GO
> |
Passo 2: Preparar um vestígio para capturar o relatório do processo bloqueado. Execute-o como um trace do lado do servidor.
- Deve conter um evento: Erros e Avisos – > Relatório de Processos Bloqueados.
- Necessita apenas de duas colunas: Text, spid
Este exemplo de rastreamento lateral do servidor será executado por cinco minutos e automaticamente parará. Você deve alterar o caminho para um diretório SQL Server válido tem permissão de escrita.
|
1
2
3
4
5
6
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
— Criado por: SQL Server 2012 Profiler
— Criar uma Fila
declarar @rc int
declarar @TraceID int
declarar @maxfilesize bigint
declarar @DateTime datetime
———Adicionou uma função aqui:
set @DateTime = DATEADD(mi,5,getdate()); /* Correr durante cinco minutos */
set @maxfilesize = 5
— Por favor substitua o texto InsertFileNameHere, por um apropriado
— filename prefixado por um caminho, e.g., c:\MinhaPastaMyTrace. A extensão .trc
— será anexada automaticamente ao nome do arquivo. Se estiver a escrever de
— servidor remoto para drive local, por favor use UNC path e certifique-se que o servidor tem
— escreva o acesso à sua partilha de rede
———–Configure o meu nome de ficheiro aqui:
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Arquivo e Tabela do lado do cliente não podem ser scripts
— Defina os eventos
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Definir os Filtros
declarar @intfilter int
declarar @bigintfilter bigint
— Definir o estado do traço para começar
executar sp_trace_setstatus @TraceID, 1
— mostrar trace id para referências futuras
seleccionar TraceID=@TraceID
goto finish
erro:
seleccionar ErrorCode=@rc
terminar:
go
|
Passo 3: Olha para os traços em execução e obtém a identificação do traço:
|
1
2
|
SELECT * do sys.traços;
GO
|
Parar um traço, se necessário
|
1
2
3
4
5
|
–Plug no traceid correto da consulta acima
EXEC sp_trace_setstatus @traceid =2, @status = 0; — parar o traço (assumindo o seu ID de traço 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — fechar o traço (assumindo o seu ID de traço 2)
GO
|
Step 4: Ler os dados do relatório do processo bloqueado.
Nesta amostra usamos o visualizador de relatório de processo bloqueado grátis por Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;
GO
|
Passo 5: Limpeza!
Não se esqueça deste passo.
|
1
2
3
4
5
6
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Certifique-se de que o seu vestígio desapareceu
SELECT * do sys.traces;
GO
—Desligue o relatório do processo bloqueado quando não estiver usando-o.
–Certifique-se de não ter nenhuma alteração pendente
SELECT *
FROM sys.configurações
WHERE value <> value_in_use;
GO
exec sp_configure ‘blocked process threshold (s)’, 0;
GO
RECONFIGURA
GO
exec sp_configure ‘blocked process threshold (s)’;
GO
|
