GROC (Getting Rid Of Cursors) part one: Cursor Lifecycle

This is the first of the GROC-files: a list of articles about TSQL server-side cursors and how to get rid of them.

In the late '80s cursors were added to the ANSI SQL standard, basically to allow the Cobol/PL1/IMSDB developers of that time (like me) to program against a relational database using the same constructs as in their structured programs and hierarchical databases: DO WHILE and FOR NEXT loops. So in a sense you can consider them as a backward compatibility feature: they are there for your development comfort, not for performance reasons. Indeed in most -but not all!- cases cursor based logic will be outperformed by well written Relational Algebra.

In SQL Server cursors are implemented as a native data type, so they can be referred to by variables of this type, or returned as an output parameter of a stored procedure.

In this article I'll focus on the life cycle of cursors. Cursors live (and hence consume resources) from the moment that they are declared, until they are deallocated - explicitly by you, or automatically by SQL Server. To find out whether or not there are cursors hanging around, you can monitor SQL Server's Performance Counters from the category SQLServer:Cursor Manager. You can access these counters from TSQL:

/***************************************/

/* Cursor-related Performance Counters */

/***************************************/

SELECT DISTINCT * 

  FROM sys.dm_os_performance_counters

 WHERE [object_name] LIKE 'SQLServer:Cursor%'

GO

I created a small function on top of some the performance counters, and called it a couple of times during the lifecycle of a pretty trivial cursor. Run the script, modify it (see included comments) and run it again. The script proves that cursors can remain alive (and consume resources) a lot longer than you might expect, because:

• a global cursor survives batches, and
• a cursor is not really deallocated if there are variables referencing it.

So here is some advice to keep the lifetime of a cursor as short as possible:

• Open any cursor as late as possible,
• close and deallocate any cursor as soon as possible,
• explicitly use LOCAL for a locally scoped cursor - otherwise you depend on the CURSOR_DEFAULT database setting, and
• be careful when referring to cursors via variables.

Here we go with the full test script:

/*************************/

/*                       */

/* Cursor Lifecycle Demo */

/*                       */

/*************************/

/***************/

/* Preparation */

/***************/

ALTER DATABASE AdventureWorks SET CURSOR_DEFAULT LOCAL -- or GLOBAL

GO

USE AdventureWorks

GO

CREATE SCHEMA DockOfTheBay

GO

CREATE FUNCTION DockOfTheBay.ufn_CursorsInScope

(    

    @Step AS NVARCHAR(25)

)

RETURNS TABLE 

AS

RETURN 

(

SELECT @Step AS [Step], instance_name AS [Cursor Type], cntr_value AS [Number]

  FROM sys.dm_os_performance_counters

 WHERE counter_name = 'Active cursors'

   AND instance_name LIKE 'TSQL%Cursor%'

)

GO

/*************/

/* Execution */

/*************/

-- Declare variables for each field

DECLARE @Name nvarchar(50)

-- Show that cursor is not yet there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Start')

-- Declare the cursor: 

--   name, scrolling behavior, scope, and 

--   the query that produces the resultset    

DECLARE c1 CURSOR READ_ONLY LOCAL /* change to 'GLOBAL', or remove, and compare results */

FOR

SELECT [Name]

  FROM Production.Product

-- Show that cursor is already there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Declared')

-- Open cursor: 

--    populates the resultset

OPEN c1

-- Show that cursor is already there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Opened')

-- Fetch Cursor:

--    reads the next row of the resultset into variables

FETCH NEXT FROM c1 -- Some types also allow 'FIRST', 'LAST' and/or 'PREVIOUS'

INTO @Name

-- Loop through resultset

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Body of the cursor

    PRINT @Name -- Away to the Messages Pane

    -- Fetch Cursor:

    --    reads the next row of the resultset into variables

    FETCH NEXT FROM c1

    INTO @Name

END

-- Show that cursor is still there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Fetched')

-- GO 

-- Uncomment the 'GO' statement on the previous line, and compare results:

--    at the end of a batch a local cursor is cleaned up,

--    but a global cursor continues to live.

-- Close cursor: 

--    releases the result set and the locks

CLOSE c1

BEGIN TRY

    OPEN c1 -- Only successful if still in scope: local and in the batch

    SELECT 'LOOK: ' AS [ ], 'The cursor was reopened' As [Message]

    CLOSE c1

END TRY

BEGIN CATCH

    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]

END CATCH

-- Show cursor is still there

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Closed')

-- Create a reference in a variable

DECLARE @c2 CURSOR

SET @c2 = c1

-- Deallocate cursor: 

--    removes the reference and -if it was the last one- all remaining resources

DEALLOCATE c1

-- Show cursor is still there, even after deallocation

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated')

-- Try to reopen

BEGIN TRY

    OPEN @c2 

    SELECT 'LOOK: ' AS [ ], 'I reopened a deallocated cursor' As [Message]

    CLOSE @c2

END TRY

BEGIN CATCH

    SELECT 'OOPS: ' AS [ ], ERROR_MESSAGE() As [Message]

END CATCH

-- Deallocate cursor: 

--    removes the reference and -if it was the last one- all remaining resources

DEALLOCATE @c2

-- Show cursor is really gone now

SELECT * FROM DockOfTheBay.ufn_CursorsInScope('Deallocated - last ref')

GO

/***********/

/* Cleanup */

/***********/

DROP FUNCTION DockOfTheBay.ufn_CursorsInScope

DROP SCHEMA DockOfTheBay

GO