Son muchas las ocasiones en las que ante un problema de bloqueos podemos sentirnos impotentes, la falta de información de que está pasando puede atenazarnos e impedirnos hacer nuestra labor, descubrir quien bloquea y que está haciendo.
Es cierto que a través de procedimientos del sistema, como sp_who o sp_who2 y algunas versiones que circulan por la red podemos tener cierta información de este tipo de cosas. La verdad es que yo nunca fuí muy fan de esos métodos, me parece que podemos personalizar más y mejor la información que nos da el sistema y podemos encontrar exactamente lo que estamos buscando.
Voy a poner aquí el resultado final, es decir el código terminado que determina quien bloquea y que hace, pero en las próximas lineas contaré el porqué de cada una de las decisiones.
|
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 |
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; |
lo primero que puede notar un observador detenido es que mientras que las consultas bloqueadas se obtienen de la DMV sys_dm_exec_requests, los bloqueadores se obtienen sin embargo de exec_conections, el motivo es que el bloqueador puede haber terminado su ejecución, al no estar bloqueado y por tanto el comando desaparece de las peticiones actuales, por lo que no podríamos obtener mas datos de la consulta que nos bloquea, mientras que usando el campo most_recent_sql_handle de la dmv connections podemos obtener esa información.
Se podría obtener tambien el plan de ejecución del bloqueador, pero en este momento no me parece relevante.
En los próximos días escribiré algunas mejoras a este procedimiento.
En el video de demostración, verás, no solamente como usar este comando sino también como obtener el plan de ejecución y visualizarlo en SQL Server Management Studio.
Os dejo el video de demostración
Hasta el próximo!