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
| 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, 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', 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', 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.
