A partir de la consulta que detecta quien bloquea y que hace podemos construir una forma algo más atrevida de monitorización de bloqueos. La idea es la siguiente, con el query que detecta los bloqueos, guardamos el resultado en una tabla temporal. y esperamos un tiempo prudencial -y configurable- y volvemos a ejecutar la misma sentencia. Si los mismos procesos siguen bloqueados por los mismos actores, es tiempo de reportar ese bloqueo. Es decir, es normal que en un momento del tiempo haya un bloqueo, es cosa de la concurrencia, pero no es normal que ese bloqueo dure mucho (el cuanto es ese mucho depende de la aplicación).
En el caso de que el bloqueo persista, se notifica, en nuestro caso a través de mail. Si has entendido todos estos conceptos y solo quieres el código…
|
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 |
CREATE PROCEDURE DetectaBloqueos @DelayLength CHAR(8) = '00:00:02', @enviamail BIT = 0 AS BEGIN /* 1.- guardamos la lista de bloqueos 2.- esperamos el tiempo que recibimos por parámetro, 2 segundos de forma predeterminada 3.- comprobamos tras ese tiempo que los bloqueos siguen activos 4.- mostramos esos bloqueos. 5.- opcional mandamos un mail con los datos */ WITH bloqueado AS (SELECT er.session_id, request_id, start_time, blocking_session_id, DATEDIFF(second, start_time, GETDATE()) SecondRunning, er.status, er.database_id, DB_NAME(er.database_id) DatabaseName, host_name, program_name, login_name, nt_domain, nt_user_name, command.text FullCommand, Execution_plan.query_plan AS QueryPlan FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(sql_handle) command OUTER APPLY sys.dm_exec_text_query_plan(plan_handle, DEFAULT, DEFAULT) Execution_plan WHERE blocking_session_id != 0), bloqueador AS (SELECT COALESCE(er.session_id, es.session_id) session_idbloqueador, host_name host_namebloqueador, program_name program_namebloqueador, login_name login_namebloqueador, nt_domain nt_domainbloqueador, nt_user_name nt_user_namebloqueador, command.text FullCommandBloqueador FROM sys.dm_exec_connections er RIGHT JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) command) SELECT * INTO #bloqueados FROM bloqueado INNER JOIN bloqueador ON bloqueado.blocking_session_id = bloqueador.session_idbloqueador; WAITFOR DELAY @DelayLength; WITH bloqueado AS (SELECT er.session_id, request_id, start_time, blocking_session_id, DATEDIFF(second, start_time, GETDATE()) SecondRunning, er.status, er.database_id, DB_NAME(er.database_id) DatabaseName, host_name, program_name, login_name, nt_domain, nt_user_name, command.text FullCommand, Execution_plan.query_plan AS QueryPlan FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(sql_handle) command OUTER APPLY sys.dm_exec_text_query_plan(plan_handle, DEFAULT, DEFAULT) Execution_plan WHERE blocking_session_id != 0), bloqueador AS (SELECT COALESCE(er.session_id, es.session_id) session_idbloqueador, host_name host_namebloqueador, program_name program_namebloqueador, login_name login_namebloqueador, nt_domain nt_domainbloqueador, nt_user_name nt_user_namebloqueador, command.text FullCommandBloqueador FROM sys.dm_exec_connections er RIGHT JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) command) SELECT * INTO #bloqueados2 FROM bloqueado INNER JOIN bloqueador ON bloqueado.blocking_session_id = bloqueador.session_idbloqueador; IF ( SELECT COUNT(*) FROM #bloqueados b INNER JOIN #bloqueados2 b2 ON b.session_id = b2.session_id AND b.blocking_session_id = b2.blocking_session_id ) != 0 BEGIN /* hay bloqueos que duran más del tiempo límite establecido*/ IF(@enviamail = 1) BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Miguel', @recipients = 'miguel.egea@gmail.com', @query = ' WITH bloqueado AS (SELECT er.session_id, request_id, start_time, blocking_session_id, DATEDIFF(second, start_time, GETDATE()) SecondRunning, er.status, er.database_id, DB_NAME(er.database_id) DatabaseName, host_name, program_name, login_name, nt_domain, nt_user_name, command.text FullCommand, Execution_plan.query_plan AS QueryPlan FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(sql_handle) command OUTER APPLY sys.dm_exec_text_query_plan(plan_handle, DEFAULT, DEFAULT) Execution_plan WHERE blocking_session_id != 0), bloqueador AS (SELECT COALESCE(er.session_id, es.session_id) session_idbloqueador, host_name host_namebloqueador, program_name program_namebloqueador, login_name login_namebloqueador, nt_domain nt_domainbloqueador, nt_user_name nt_user_namebloqueador, command.text FullCommandBloqueador FROM sys.dm_exec_connections er RIGHT JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) command) SELECT * FROM bloqueado INNER JOIN bloqueador ON bloqueado.blocking_session_id = bloqueador.session_idbloqueador;', @subject = 'informe de bloqueos', @attach_query_result_as_file = 1; END; ELSE BEGIN SELECT b2.* FROM #bloqueados b INNER JOIN #bloqueados2 b2 ON b.session_id = b2.session_id AND b.blocking_session_id = b2.blocking_session_id; END; END; END; |
Si poor el contrario quieres entenderlo un poquito mejor y ver como funciona y como se agendaría, aquí tienes el video de demostración.
Estimado Miguel.
Excelente tu código, ya lo estoy implementando.
Una acotación, en vez de utilizar un delay porque no usar un Where
WHERE CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, start_time, GETDATE()), 0), 114) > @DelayLength
Saludos Cordiales.
Cristian Peña M.
El motivo de usar un delay es porque si no esperamos, si no hacemos que simplemente unos segundos no se haga nada, siempre estarán presentes los mismos bloqueos entre el momento inicial y el del segundo query. La idea es que un bloequeo «corto» de , por ejemplo , 1 sg o así puede ser normal. Lo que no es normal es que permanezca mucho tiempo. Me alegro mucho que te guste!