Ese asunto de los cursores

Introducción

La idea básica de este post es avanzar un poco más en el análisis de las razones  por las cuales es recomendable evitar el uso de cursores.

Razones principales del impacto de los cursores server-side en los recursos del Database Engine

El Database Engine esta optimizado para procesar conjuntos de registros, de la misma forma que el lenguaje T-SQL es un lenguaje de tipo imperativo orientado a conjuntos de registros, donde los comandos indican el resultado a obtener en vez de detallar el camino para obtenerlo. En suma, tanto el Engine como T-SQL están diseñados de acuerdo con un esquema set-oriented.

Los cursores son un recurso programático que permite recuperar en memoria un conjunto de registros y procesarlos un registro por vez, es decir, operan con un esquema de tipo record-oriented.

En el caso de cursores implementados en T-SQL, para que el Database Engine pueda lograr que este tipo de recursos opere en forma adecuada y lo haga en armonía con el resto de sí mismo, necesita contener a los cursores en un entorno de ejecución propio, adecuado para el esquema record-oriented, que permita conservar la consistencia y armonía entre el cursor y el resto de las cosas que el Engine controla, que operan con el modelo set-oriented.

Dicho entorno de ejecución consume en forma significativa recursos compartidos y escasos, y esta es la razón principal por lo cual es tan generalizada la recomendación de evitar el uso de cursores, en particular, cursores server-side, tal como es el caso de los cursores implementados en T-SQL.

Por su parte, en el caso de los cursores server-side, la metadata del cursor, necesaria para su ejecución, es almacenada en memoria del mismo server, lo que agrava aún más la situación en relación al consumo de recursos compartidos y escasos de la instancia.

Otros problemas asociados al uso de cursores

Además de los problemas ya mencionados en relación al uso de cursores en T-SQL, es común que surjan algunos otros problemas que afectan al uso de recursos en una instancia durante la ejecución de una rutina basada en cursores T-SQL.

Estos problemas frecuentes son consecuencia del hecho que no se configura el cursor en forma adecuada según los requerimientos de la rutina que lo contiene.

Para ilustrar este tipo de problemas, usaremos como base de nuestro análisis un ejemplo típico de uso de cursores que podemos obtener haciendo una búsqueda en Internet.

Un ejemplo “clásico” podria ser algo así:

DECLARE @name VARCHAR(50) — database name

DECLARE @path VARCHAR(256) — path for backup files

DECLARE @fileName VARCHAR(256) — filename for backup

DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘C:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Vamos a usar para el análisis un ejemplo de cursores similar, con el mismo tipo de configuración de cursor no adecuada.

Vamos a usar la base de datos AventureWorks para este ejemplo (en realidad, una copia de la misma cuyo nombre es AW).

A fin de mostrar como afecta el uso de una configuración no adecuada del cursor, el ejemplo de código no cierra el cursor, es decir, lo deja abierto para ver que pasa con la base de datos afectada por un cursor con este tipo de configuración durante la ejecución del mismo.

El código de la rutina de ejemplo es el siguiente:

DECLARE @OrderQty int

DECLARE @QtyIncrease decimal(8, 3)

USE AW

DECLARE db_cursor CURSOR FOR

SELECT OrderQty from Sales_SalesOrderDetail order by SalesOrderDetailId

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @OrderQty

BEGIN

SET @QtyIncrease = (@OrderQty * 1.25)

PRINT @QtyIncrease

FETCH NEXT FROM db_cursor INTO @OrderQty

END

Para analizar como un cursor con esta configuración afecta a la base de datos y a la instancia involucradas en su ejecución, usaremos el siguiente query con algunas DMVs adecuadas para el análisis de ejecución de cursores:

SELECT c.creation_time, c.cursor_id, c.name, c.session_id

,tl.resource_type

,tl.resource_description

,tl.request_session_id

,tl.request_mode

,tl.request_type

,tl.request_status

,tl.request_reference_count

,tl.request_lifetime

,tl.request_owner_type

,s.transaction_isolation_level

,s.login_time

,s.last_request_start_time

,s.last_request_end_time

,s.status

,s.program_name

,s.nt_user_name

FROM sys.dm_exec_cursors(0) AS c

inner JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id

inner join sys.dm_tran_locks tl on

tl.request_session_id = s.session_id

GO

El resultado de este query es algo así:

 Result

Como se puede apreciar, durante la ejecución de un cursor con este tipo de configuración, el cursor aplica un lock a la base de datos que contiene a las tablas involucradas.

Dado que a propósito no hemos cerrado el cursor, podemos verificar el lock de la base de datos completa intentando hacer algún cambio a la misma que ponga de manifiesto dicho lock, como por ejemplo, intentar renombrarla.

Al intentar renombrar a la base de datos AW, despues de unos segundos SSMS retorna el siguiente mensaje de error:

Error_Message

La falta de la correcta configuración de los cursores server-side es un error muy común y generalizado.

Sin embargo, en este post no le dedicaremos tiempo a revisar los detalles de una correcta configuración de cursores server-side, dado que el objetivo principal del post es exponer las razones por las cuales no deberíamos usar cursores, y si hemos puesto el foco en exponer con ejemplos concretos algunas de las consecuencias negativas que explican porque no deberíamos usar cursores.

Saludos, GEN

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s