Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.740 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 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') November 5th, 2020
  • October 16th, 2017
  • 0
  • Database DMVs and Catalog Views Maintenance Monitoring SQL Server

SQL Server - How to create an email alert of locks and sessions locked on instance using DMV's

Views: 2.075
Reading Time: 6 minutes

Hey guys,
All right with you ?

In this post, I want to share with you a solution (as many as possible) to create a SQL Server instance lock monitoring and alerting that can let you know when one or more sessions have been in lock (waiting for some feature) for more than X minutes .

Those working in critical environments know how impactful an instance lock can be for several minutes by locking one or more sessions in the database, or even in routines that run out of business hours and end up not being executed for some time. blocking these.

I recently came across such a problem where a change was made to a Stored Procedure outside of business hours while it was running through a SQL Agent job. This routine eventually crashed into a critical and heavily accessed table in production, and sessions accessing that table began to queue up generating hundreds of locks in the bank, bringing a giant impact to the bank routines that were only realized the next day. Several routines that should have been executed at dawn ended up not executing, waiting for the lock to finish.

And it could all have been resolved with a simple lock alert on the phone, since the lock started not too late and some team DBA could have acted.

My idea is to share this alert so that it doesn't happen to your SQL Server banks and is always proactive with the issues that occur in your environment.

For this solution, I will use a Stored Procedure that will collect data from SQL Server's DMV's to find the lock sessions and the block leaders (sessions causing the locks), generating an alert for the bank team informing the problem in a clear, simple and objective way.

How to create a locks alert on instance

To create the locks alert, you can use the source code provided below and create a SQL Agent job to perform this Stored Procedure every X minutes, as needed.

You can also set the time to determine how long a session can be locked until notified (@Qt_Minutes_Lock - I configured 3 minutes). This is to avoid many false emails and alerts.

In this monitoring, an alert is only sent when the last situation is “OK”, that is, no lock in the environment, or when the longest time in lock reaches the limit defined between the alerts (@Qt_Minutos_Entre_Alerts - I configured 30 minutes between alerts), that is, the alerts are not being sent with each job execution, even if the sessions continue to lock.

Two features that greatly help with this monitoring are the nested_level and blocked_session_count columns:

  • nested_level: level of hierarchy of locks. The 1 level are the sessions that are directly crashing the 2 level sessions and indirectly all sessions from the 3 levels onwards, and so on.
  • blocked_session_count: Column that shows how many other sessions this particular session is locking. Ex: If a session has this column filled with the value 8, it means that there are 8 locked sessions waiting for resources to be released from that session.

Here are some links that can help you improve this alert and better understand what was done:

  • SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail)
  • How to export data from a SQL Server table to HTML
  • SQL Server - How to Email a Query Result in HTML Format Using CLR (C #)
  • Using the Pushbullet API to send SMS messages in C #, PHP, Java, or SQL Server (with CLR)
  • SQL Server - How to Send SMS Messages Using the CLR (C #) and the More Result API (PG Solutions)
  • SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #)
  • SQL Server - How to Integrate Database with Telegram and Send Messages Using CLR (C #)
  • SQL Server - How to Integrate Database with Ryver and Send Messages Using CLR (C #)

Monitoring Stored Procedure Source Code

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

Observation: The CONCAT function is available from SQL Server 2012. If you use a version earlier than this, you will need to remove the CONCAT function from the code and use simple concatenation (using +).

Alert Email Example

That's it folks!
Regards and see you next post.

tags: alertblocklocklocksmonitoringsqlsql server

You may also like ...

  • SQL Server - Performance Tuning Tips: What is the Difference Between Seek Predicate and Predicate?

  • SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

  • SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)

  • Next SQL Server - How to Practically Share Your Execution Plans on the Web
  • Previous SQL Server - How to Create Error and Exception Tracking in Your Database Using Extended Events (XE)

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.