post icon

Buscar cursores abiertos en T-SQL para optimizar recursos

Ayer en el Twitter me preguntaban sobre el excesivo consumo de memoria RAM por parte de MSSQL Server y qué se podía hacer para evitarlo. No estuve muy interiorizado con el problema en sí, pero me sirvió para inspirarme y escribir un articulo más sobre búsqueda y corrección de problemas en la base de datos.

El consumo de RAM en SQL Server no siempre es un problema, ya que si se configuró para que consuma todos los recursos que están al alcance, lo hará. Ya que comenzará a guardar en caché las consultas que se ejecutan con mayor frecuencia para responder con mayor velocidad a la demanda. Pero no todo es color de rosa, éste síntoma también podría deberse a una mala utilización de recursos y/o mala praxis de programación.

Existen varias cosas que podemos hacer para detectar problemas, en cuanto a la mala programación se puede realizar una inspección minuciosa de los planes de ejecución para optimizarlos, revisar las estructuras si hay índices donde corresponden, el factor de relleno de los mismos, tener estadísticas actualizadas, fragmentación, etc.

En casos muy típicos en ambientes de producción es que a veces nos olvidamos de cerrar y destruir los cursores que estamos utilizando en las querys, dichos cursores pueden llegar a sernos muy útiles pero siempre deben ser cerrados y destruidos una vez finalizada su tarea ya que normalmente se utilizan en querys complejos y con muchos datos. Mantenerlos abiertos podría producir un colapso de los recursos y verse ralentizado el servicio.

Imagínate que tiene miles de procedimientos almacenados en varias bases de datos, y varios de ellos utilizan cursores, además de tener una concurrencia alta de clientes, ¿cómo podrías saber cuál de todos los cursores está causando problemas? o ¿Qué sesión/usuario está utilizando dicho cursor?

El motor desde la versión 2005 hasta la 2012 nos ofrece un función de sistema que alcanza a todas las bases de datos que estén montadas y activas en nuestra instancia, la cual nos devuelve un montón de información útil y relevante para detectar los cursores que están creados (ya sea que estén abiertos o cerrados).

sys.dm_exec_cursors(session_id | 0)

La función puede recibir un SPID o Id de Sesión para listar todos los cursores de esa sesión, o bien si recibe 0 (cero) lista todos los cursores de todas las sesiones activas.

Ésta función devuelve una tabla, por lo tanto puede utilizarse con la cláusula SELECT, JOIN, etc. como cualquier otro conjunto de datos.

select * from  sys.dm_exec_cursors(0);

El siguiente ejemplo listaria todos los cursores que están abiertos por más de 8 horas seguidas

SELECT creation_time, cursor_id, name, c.session_id, login_name<br />FROM sys.dm_exec_cursors(0) AS c<br />JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id<br />WHERE DATEDIFF(hh, c.creation_time, GETDATE()) &gt; 8;

No todo lo que salga aquí significa que está mal, simplemente quiere decir que existen dichos cursores para bien o para mal, a partir de éstos datos podremos analizar si se debe replantear la programación o simplemente verificar si tenemos el código que hace el CLOSE y el DEALLOCATE.

Con el SPID que devuelven éstas querys, se puede utilizar en conjunto con SP_WHO y SP_WHO2 para saber desde donde y que query o SP están utilizando para localizar el foco del posible problema.

sp_who;

 

sp_who 34;

 

sp_who2;

 

sp_who 45;

Incluso si es necesario podemos matar esas sesiones y cursores con ayuda del KILL

kill 34;

 

kill 45;

Para saber que valor posible y que significa cada columna de la tabla devuelta pueden consultar la ayuda oficial del MSDN.

Cómo se habrán dado cuenta, con ésto solo podrán paliar el problema momentáneamente, ya que terminarán la sesión del cliente, pero si vuelve a ejecutar volverá a ocurrir. Para evitarlo para siempre debemos identificar en que procedimiento, funcion, trigger u objeto se esté utilizando el cursor, para ello vamos a tomar el valor sql_handle (ejemplo: 0x03000C0050B04526E4F21101E59F00000100000000000000) de la primer query (sys.dm_exec_cursors) y lo pasaremos como parametro a la siguiente query:

select
    *
from
    sys.dm_exec_sql_text(0x03000C0050B04526E4F21101E59F00000100000000000000)

Con éso verán el script del objeto que invocó y dejó abierto el cursor, sobra decir que allí agregarán el CLOSE y DEALLOCATE correspondientes.

Comentarios desde Facebook:

Sin Comentarios aun, puedes tú ser el primero en comentar!

Deja tu Comentario

Responder