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

2 comments:

  1. BlueHost is definitely the best website hosting provider with plans for all of your hosting needs.

    ReplyDelete
  2. Quantum Binary Signals

    Get professional trading signals delivered to your mobile phone every day.

    Start following our trades right now & earn up to 270% a day.

    ReplyDelete