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
|
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://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://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://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 |
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.
