O que é SQL Server Lock e Block

O que é SQL Server Lock e Block?

Por padrão, o SQL Server usa Locking de forma “pessimista” – suas queries são naturalmente protetoras. Isso pode levar a problemas de Blocking que impeçam você de escalonar seu banco de dados SQL Server.

O artigo está separado em: 

  • Contadores de desempenho relacionados a Lock

  • Scripts para usar Blocked Process Report

 

Esses são alguns contadores de performance que podem ser muito úteis para configurar alerta no SQL Server. Com base nesses contadores o SQL Server pode notificá-lo quando o bloqueio ultrapassar os limites definidos.

  • SQL Server: General Statistics – Processes Blocked
  • SQL Server: Locks – Lock Wait Time (ms)
  • SQL Server: Locks – Number of Deadlocks/sec
contadores de performance do banco sql server

Script para usar Blocked Process Report

O “Blocked Process Report” existe há muito tempo no SQL Server – e ainda é uma ferramenta útil e usada por consultoria de banco de dados e consultores SQL Server. 

Por padrão no SQL Server, o deadlock monitora a cada 5 segundos para verificar se suas queries estão em um “deadly embrace”. Você pode ativar uma opção no SQL Server para verificar o blocking e gerar um relatório.

AVISO: Você só deve ativar o relatório de Blocked Process quando o monitoramento for superior a cinco segundos. A Microsoft avisa que, se você definir de 1 a 4 segundos, poderá fazer com que o monitor de deadlock fique ativo continuamente e prejudique o desempenho do banco de dados.

Para usar o Blocked Process Report, é necessário configurar um trace que procure o evento “Relatório de Processo Bloqueado” em “Erros e Avisos”. Recomendamos usar um server side trace e usá-lo apenas quando for necessário.

Os scripts nesse artigo, são exemplos de como realizar essas tarefas – você deve testar e revisar de acordo com o seu ambiente de banco de dados. Sempre utilize ambiente de teste e/ou homologação e tenha backup de seu ambiente.

Lembre-se, a execução dos scripts é por sua própria conta e risco.

7 Pontos para Observar em Serviços Gerenciados de Banco de Dados

Com esse guia você pode reduzir problemas com os serviços gerenciados e consultoria de banco de dados

Então, vamos lá!

Etapa 1 : ative Blocked Process Report

 

–Make sure you don’t have any pending changes

SELECT *

FROM sys.configurations

WHERE value <> value_in_use;

GO

exec sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE

GO

 

exec sp_configure ‘blocked process threshold (s)’, 20;

GO

RECONFIGURE

GO

Etapa 2 : configurar o rastreamento para o relatório de processo bloqueado. Execute-o como  server side trace.

  • Deve conter um evento: Errors and Warnings -> Blocked Process Report.
  • Precisa de apenas duas colunas: Text, spid

Este server side trace será executado por cinco minutos e parará automaticamente. Você deve alterar o caminho para um diretório em que o SQL Server tenha permissão de gravação.

 

— Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

 

———Added a function here:

set @DateTime = DATEADD(mi,5,getdate()); /* Run for five minutes */

set @maxfilesize = 5

 

— Please replace the text InsertFileNameHere, with an appropriate

— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

— will be appended to the filename automatically. If you are writing from

— remote server to local drive, please use UNC path and make sure server has

— write access to your network share

 

———–Set my filename here:

exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime

if (@rc != 0) goto error

 

— Client side File and Table cannot be scripted

 

— Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 137, 1, @on

exec sp_trace_setevent @TraceID, 137, 12, @on

 

— Set the Filters

declare @intfilter int

declare @bigintfilter bigint

 

— Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

 

— display trace id for future references

select [email protected]

goto finish

 

error:

select [email protected]

 

finish:

go

Etapa 3 : Olhe os traces em execução e pegue o trace ID:

SELECT * from sys.traces;

GO

Pare um rastreamento, se necessário

–Plug in the correct traceid from the query above

EXEC sp_trace_setstatus @traceid =2, @status = 0; — stop trace (assuming it’s trace ID 2)
GO

EXEC sp_trace_setstatus @traceid =2, @status = 2; — close trace (assuming it’s trace ID 2)
GO

Etapa 4 : Agora leia os  dados gerados pelo relatório do processos bloqueados.

Neste exemplo, vamos usar o visualizador de relatório de processo bloqueado gratuito de Michael J Swart .

exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’;

GO

Etapa 5 : Limpe!

Não se esqueça desta etapa.

–Make sure your trace is gone
SELECT * from sys.traces;
GO

–Turn off the blocked process report when you’re not using it.
–Make sure you don’t have any pending changes
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO

exec sp_configure ‘blocked process threshold (s)’, 0;
GO
RECONFIGURE
GO

exec sp_configure ‘blocked process threshold (s)’;
GO

Espero que nosso artigo tenha ajudado você a entender mais sobre Block e Locking.

Caso precise de uma consultoria em banco de dados SQL Server ou Oracle, fale conosco.

Fale com um especialista

Fale com um especialista agora, e tenha a melhor solução de TI para sua empresa.

Acompanhe a Tripletech nas redes sociais: