Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.436 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 16 de outubro de 2017
  • 0
  • Banco de Dados DMVs e Views de Catálogo Manutenção Monitoramento SQL Server

SQL Server – Como criar um alerta por e-mail de locks e sessões bloqueadas na instância utilizando DMV’s

Visualizações: 1.746
Tempo de Leitura: 6 minutos

Fala galera,
Tudo bem com vocês ?

Nesse post, eu quero compartilhar com vocês uma solução (de muitas possíveis) para criar um monitoramento e alerta de locks na instância SQL Server, que poderá te informar quando uma ou mais sessões estão em lock (aguardando algum recurso) há mais de X minutos.

Quem trabalha com ambientes críticos sabe o quão impactante pode ser um lock na instância há vários minutos travando uma ou mais sessões no banco de dados, ou mesmo, em rotinas que são executadas fora do horário comercial e que acabam não sendo executadas por conta de algum bloqueio desses.

Recentemente me deparei com um problema desses, onde foi realizada uma alteração numa Stored Procedure fora do horário comercial, enquanto ela estava em execução através de um job do SQL Agent. Essa rotina acabou travando uma tabela crítica e muito acessada em produção, e as sessões que acessavam essa tabela começaram a se enfileirar gerando centenas de locks no banco, trazendo um impacto gigante para as rotinas de banco, que só foram percebidas no dia seguinte.. Várias rotinas que deveriam ter sido executadas de madrugada acabaram não sendo executadas, aguardando o lock terminar.

E tudo poderia ter sido resolvido com um simples alerta de lock no celular, uma vez que o início do lock não começou muito tarde e algum DBA da equipe poderia ter atuado.

A minha ideia é compartilhar esse alerta para que isso não aconteça com seus bancos SQL Server e sejam sempre pró-ativos com os problemas que ocorrem no seu ambiente.

Para essa solução, vou utilizar uma Stored Procedure que irá fazer a coleta dos dados a partir de DMV’s do SQL Server para encontrar as sessões em lock e os block leaders (sessões causadoras dos locks), gerando um alerta para a equipe de banco informando o problema de forma clara, simples e objetiva.

Como criar um alerta de locks na instância

Para criar o alerta de locks, você pode utilizar o código-fonte disponibilizado logo abaixo e criar um job no SQL Agent para executar essa Stored Procedure a cada X minutos, de acordo com a sua necessidade.

Você também pode definir o tempo para determinar há quanto tempo uma sessão pode ficar em lock até ser notificada (@Qt_Minutos_Lock – eu configurei 3 minutos). Isso serve para evitar muitos e-mails e alertas em falso.

Nesse monitoramento, um alerta só é enviado quando a última situação for “OK”, ou seja, nenhum lock no ambiente, ou quando o maior tempo em lock atingir o limite definido entre os alertas (@Qt_Minutos_Entre_Alertas – eu configurei 30 minutos entre os alertas), ou seja, os alertas não ficam sendo enviados a cada execução do job, mesmo que a sessões continuem em lock.

Dois recursos que ajudam bastante nesse monitoramento, são as colunas nested_level e blocked_session_count:

  • nested_level: nível de hierarquia dos locks. O nível 1 são as sessões que estão travando diretamente as sessões de nível 2 e indiretamente todas as sessões dos níveis 3 em diante, e assim sucessivamente.
  • blocked_session_count: Coluna que mostra a quantidade de outras sessões que essa sessão específica está lockando. Ex: Se uma sessão está com essa coluna preenchida com o valor 8, isso quer dizer que existem 8 sessões lockadas aguardando a liberação de recursos dessa sessão.

Vou deixar aqui alguns links que podem ajudá-los a melhorar esse alerta e entender melhor o que foi feito:

  • SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail)
  • Como exportar dados de uma tabela do SQL Server para HTML
  • SQL Server – Como enviar o resultado de uma query por e-mail no formato HTML utilizando o CLR (C#)
  • Como utilizar a API do Pushbullet para enviar torpedos SMS no C#, PHP, Java ou pelo SQL Server (com CLR)
  • SQL Server – Como enviar Torpedos SMS utilizando o CLR (C#) e a API da Mais Resultado (PG Soluções)
  • SQL Server – Como fazer uma integração do banco de dados com o Slack e enviar mensagens utilizando o CLR (C#)
  • SQL Server – Como fazer uma integração do banco de dados com o Telegram e enviar mensagens utilizando o CLR (C#)
  • SQL Server – Como fazer uma integração do banco de dados com o Ryver e enviar mensagens utilizando CLR (C#)

Código-fonte da Stored Procedure do Monitoramento

Transact-SQL
1
2
3
4
5
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
CREATE PROCEDURE [dbo].[stpMonitoramento_Locks]
AS BEGIN
    
    IF (OBJECT_ID('dbo.Alerta') IS NULL)
    BEGIN
 
        CREATE TABLE dbo.Alerta (
            Id_Alerta INT NOT NULL IDENTITY(1, 1),
            Nm_Alerta VARCHAR(200) NULL,
            Ds_Mensagem VARCHAR(2000) NULL,
            Fl_Tipo TINYINT NULL,
            Dt_Alerta DATETIME NULL DEFAULT (GETDATE())
        ) WITH (DATA_COMPRESSION = PAGE)
 
        ALTER TABLE dbo.Alerta ADD CONSTRAINT PK_Alerta PRIMARY KEY CLUSTERED (Id_Alerta) WITH (DATA_COMPRESSION = PAGE)
 
    END
 
 
    IF (OBJECT_ID('tempdb..##Monitoramento_Locks') IS NOT NULL) DROP TABLE ##Monitoramento_Locks
    CREATE TABLE ##Monitoramento_Locks
    (
        [nested_level] INT NULL,
        [session_id] INT NOT NULL,
        [login_name] NVARCHAR(128) NOT NULL,
        [host_name] NVARCHAR(128),
        [program_name] NVARCHAR(128),
        [wait_info] NVARCHAR(128),
        [wait_time_ms] BIGINT,
        [blocking_session_id] INT,
        [blocked_session_count] INT,
        [open_transaction_count] INT NOT NULL
    )
 
    INSERT INTO ##Monitoramento_Locks
    SELECT
        NULL AS nested_level,
        A.session_id AS session_id,
        A.login_name,
        A.[host_name],
        (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) +  ')' ELSE A.[program_name] END) AS [program_name],
        '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
            WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
            WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
            ELSE ''
        END), '') AS wait_info,
        COALESCE(E.wait_duration_ms, B.wait_time) AS wait_time_ms,
        NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
        COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
        A.open_transaction_count
    FROM
        sys.dm_exec_sessions AS A WITH (NOLOCK)
        LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
        LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)
        LEFT JOIN (
            SELECT
                session_id,
                wait_type,
                wait_duration_ms,
                resource_description,
                ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
            FROM
                sys.dm_os_waiting_tasks
        ) E ON A.session_id = E.session_id AND E.Ranking = 1
        LEFT JOIN (
            SELECT
                blocking_session_id,
                COUNT(*) AS blocked_session_count
            FROM
                sys.dm_exec_requests
            WHERE
                blocking_session_id != 0
            GROUP BY
                blocking_session_id
        ) G ON A.session_id = G.blocking_session_id
    WHERE
        A.session_id > 50
        AND A.session_id <> @@SPID
        AND (NULLIF(B.blocking_session_id, 0) IS NOT NULL OR COALESCE(G.blocked_session_count, 0) > 0)
 
 
    ------------------------------------------------
    -- Gera o nível dos locks
    ------------------------------------------------
 
    UPDATE ##Monitoramento_Locks
    SET nested_level = 1
    WHERE blocking_session_id IS NULL
 
 
    DECLARE @Contador INT = 2
 
    WHILE((SELECT COUNT(*) FROM ##Monitoramento_Locks WHERE nested_level IS NULL) > 0 AND @Contador < 50)
    BEGIN
        
 
        UPDATE A
        SET
            A.nested_level = @Contador
        FROM
            ##Monitoramento_Locks A
            JOIN ##Monitoramento_Locks B ON A.blocking_session_id = B.session_id
        WHERE
            A.nested_level IS NULL
            AND B.nested_level = (@Contador - 1)
 
 
        SET @Contador += 1
 
 
    END
 
 
    UPDATE ##Monitoramento_Locks
    SET nested_level = @Contador
    WHERE nested_level IS NULL
 
 
    CREATE CLUSTERED INDEX SK01 ON ##Monitoramento_Locks(nested_level, blocked_session_count DESC, wait_time_ms DESC)
 
 
    DECLARE
        @Qt_Sessoes_Bloqueadas INT,
        @Qt_Sessoes_Bloqueadas_Total INT,
        @Fl_Ultimo_Status INT,
        @Dt_Ultimo_Alerta DATETIME,
        @Ds_Mensagem VARCHAR(MAX),
        @Ds_Assunto VARCHAR(100),
 
        -- Configurações do monitoramento
        @Qt_Minutos_Lock INT = 3,
        @Qt_Minutos_Entre_Alertas INT = 30,
        @Ds_Email_Destinatario VARCHAR(MAX) = '[email protected]'
    
 
    SELECT
        @Qt_Sessoes_Bloqueadas = COUNT(*)
    FROM
        ##Monitoramento_Locks
    WHERE
        wait_time_ms > (60000 * @Qt_Minutos_Lock)
        AND blocking_session_id IS NOT NULL
 
 
    SELECT
        @Qt_Sessoes_Bloqueadas_Total = COUNT(*)
    FROM
        ##Monitoramento_Locks
    WHERE
        blocking_session_id IS NOT NULL
 
 
 
    SELECT
        @Fl_Ultimo_Status = ISNULL(A.Fl_Tipo, 0),
        @Dt_Ultimo_Alerta = ISNULL(A.Dt_Alerta, '1900-01-01')
    FROM
        dbo.Alerta A WITH(NOLOCK)
        JOIN
        (
            SELECT
                MAX(Id_Alerta) AS Id_Alerta
            FROM
                dbo.Alerta WITH(NOLOCK)
            WHERE
                Nm_Alerta = 'Block'
        ) B ON A.Id_Alerta = B.Id_Alerta
 
 
    SELECT
        @Fl_Ultimo_Status = ISNULL(@Fl_Ultimo_Status, 0),
        @Dt_Ultimo_Alerta = ISNULL(@Dt_Ultimo_Alerta, '1900-01-01')
 
 
    
    ------------------------------------
    -- Envia o CLEAR
    ------------------------------------
 
    IF (@Fl_Ultimo_Status = 1 AND @Qt_Sessoes_Bloqueadas = 0)
    BEGIN
    
 
        SELECT
            @Ds_Mensagem = CONCAT('CLEAR: Não existem mais sessões em lock na instância ', @@SERVERNAME),
            @Ds_Assunto = 'CLEAR - [' + @@SERVERNAME + '] - Locks na instância'
        
 
        INSERT INTO dbo.Alerta
        (
            Nm_Alerta,
            Ds_Mensagem,
            Fl_Tipo,
            Dt_Alerta
        )
        SELECT
            'Block',
            @Ds_Mensagem,
            0,
            GETDATE()
        
        -- Envia alerta por e-mail
        -- https://www.dirceuresende.com/blog/como-habilitar-enviar-monitorar-emails-pelo-sql-server-sp_send_dbmail/
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'ProfileEnvioEmail',
            @recipients = @Ds_Email_Destinatario,
            @subject = @Ds_Assunto,
            @body = @Ds_Mensagem,
            @body_format = 'html',
            @from_address = '[email protected]'
        
 
    END
 
 
    ------------------------------------
    -- Envia o alerta
    ------------------------------------
 
    IF (@Qt_Sessoes_Bloqueadas > 0 AND (@Fl_Ultimo_Status = 0 OR DATEDIFF(MINUTE, @Dt_Ultimo_Alerta, GETDATE()) >= @Qt_Minutos_Entre_Alertas))
    BEGIN
 
 
        SELECT
            @Ds_Mensagem = CONCAT('ALERTA: Existe', (CASE WHEN @Qt_Sessoes_Bloqueadas > 1 THEN 'm' ELSE '' END), ' ', CAST(@Qt_Sessoes_Bloqueadas AS VARCHAR(10)), ' ', (CASE WHEN @Qt_Sessoes_Bloqueadas > 1 THEN 'sessões' ELSE 'sessão' END), ' em lock na instância ', @@SERVERNAME, ' há mais de ', CAST(@Qt_Minutos_Lock AS VARCHAR(10)), ' minutos e ', CAST(@Qt_Sessoes_Bloqueadas_Total AS VARCHAR(10)), ' ', (CASE WHEN @Qt_Sessoes_Bloqueadas_Total > 1 THEN 'sessões' ELSE 'sessão' END), ' em lock no total'),
            @Ds_Assunto = 'ALERTA - [' + @@SERVERNAME + '] - Locks na instância'
 
        
        INSERT INTO dbo.Alerta
        (
            Nm_Alerta,
            Ds_Mensagem,
            Fl_Tipo,
            Dt_Alerta
        )
        SELECT
            'Block',
            @Ds_Mensagem,
            1,
            GETDATE()
 
 
        -- https://www.dirceuresende.com/blog/como-exportar-dados-de-uma-tabela-do-sql-server-para-html/
        DECLARE @HTML VARCHAR(MAX)
        
        EXEC dbo.stpExporta_Tabela_HTML_Output
            @Ds_Tabela = '##Monitoramento_Locks', -- varchar(max)
            @Fl_Aplica_Estilo_Padrao = 1, -- bit
            @Ds_Saida = @HTML OUTPUT -- varchar(max)
 
 
        SET @Ds_Mensagem += '<br><br>' + @HTML
 
        -- Envia alerta por e-mail
        -- https://www.dirceuresende.com/blog/como-habilitar-enviar-monitorar-emails-pelo-sql-server-sp_send_dbmail/
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'ProfileEnvioEmail',
            @recipients = @Ds_Email_Destinatario,
            @subject = @Ds_Assunto,
            @body = @Ds_Mensagem,
            @body_format = 'html',
            @from_address = '[email protected]'
 
    
    END
 
 
END

Observação: A função CONCAT está disponível a partir do SQL Server 2012. Se você utiliza uma versão anterior a essa, você precisará remover a função CONCAT do código e utilizar a concatenação simples (usando +).

Exemplo de e-mail de alerta

É isso aí, pessoal!
Um abraço e até o próximo post.

Tags: alertablocklocklocksmonitoramentosqlsql server

You may also like...

  • SQL Server – Como converter uma string HTML para texto (Remover tags HTML) utilizando o CLR (C#)

  • SQL Server – Como criar um monitoramento de erros e exceções no seu banco de dados utilizando Extended Events (XE)

  • SQL Server – Permissões para utilizar OLE Automation (Sim, a documentação está errada)

  • Next SQL Server – Como compartilhar de forma prática seus planos de execução na Web
  • Previous SQL Server – Como criar um monitoramento de erros e exceções no seu banco de dados utilizando Extended Events (XE)

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.