GROC (Getting Rid Of Cursors) part two: Cursor Resources

This is the second part of the "GROC-files". I will focus on the resource consumption of cursors, especially tempdb space and locks. The variable that holds the cursor, and the FETCH INTO-variables also take some space, but I'm going to ignore that here. The following T-SQL script contains switches for most of the relevant factors:

• cursor type,
• cursor locking behavior,
• locking hints in the query,
• transaction isolation level, and
• cursor close on commit setting.

If you run the script, it will return a resultset containing the resource consumption per step, like this:



Here are a couple of observations from several runs with different settings:

• Static and keyset cursors can consume a considerable amount of tempdb memory.
  
• Dynamic cursors don't use tempdb.
  
• In higher transaction isolation levels, keyset cursors can take a lot of locks, and for a long period.
  
• In higher transaction isolation levels, static cursors can take a lot of locks, and for a long period. [They copy everything to the tempdb and lock it in the user db !].
  
• FOR UPDATE apparently has no impact on resources.
  
• Cursor owned locks only appear with the SCROLL_LOCKS setting. All other locks belong to the transaction.
  
• If the connection option to keep cursors open on a commit is set on, all locks will be kept across a commit or rollback operation.
  
• A WITH (NOLOCK) hint avoids transaction as well as cursor owned locks. [So it actually overrides the SCROLL_LOCKS setting, which might no be what you wanted !]

Anyway, some of these observations confirm (again) that cursors should be avoided in most scenarios: they use more resources than necessary and are not easily controllable.

Some final advice:

• Select as few as possible rows and columns in the cursor resultset (this advice actually applies to all possible resultsets in batches/transactions),
  
• whenever possible, prefer dynamic cursors over static ones, and
  
• explicitly set CURSOR_CLOSE_ON_COMMIT to ON in your session.

Here's the full test script:

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

/*                       */

/* Cursor Resources Demo */

/*                       */

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

USE AdventureWorks

GO

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

/* Preparation */

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

CREATE SCHEMA DockOfTheBay

GO

CREATE FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects

(

    @Session_id AS int

)

RETURNS int

AS

BEGIN

    --

    -- Returns the number of KBytes allocated to tempdb internal objects for a session

    --    

    DECLARE @Result int

    -- rem: Caching and Deferred Drop operations may influence counters

    SELECT @Result = SUM((internal_objects_alloc_page_count - internal_objects_dealloc_page_count)) * 8

      FROM  tempdb.sys.dm_db_task_space_usage 

     WHERE  session_id = @Session_id

       AND database_id = db_id('tempdb')

    RETURN @Result

END

GO

CREATE FUNCTION DockOfTheBay.ufn_Locks

(

    @Session_id AS int

)

RETURNS NVARCHAR(200)

AS

BEGIN

    --

    -- Returns the number of locks per type and owner for a session, as a comma-separated list

    --

    DECLARE @Result NVARCHAR(200)    

    ;WITH Locks AS

    (

        SELECT resource_type AS [Type]

              ,CONVERT(NVARCHAR(03), COUNT(*)) AS [Number]

              ,request_owner_type AS [Owner]

          FROM sys.dm_tran_locks

         WHERE request_session_id = @Session_id

           AND resource_type IN ('ROW', 'PAGE', 'RID', 'KEY')

         GROUP BY resource_type, request_owner_type

    )

    SELECT @Result = COALESCE(@Result + ', ', '') + [Owner] + ' ' + [Type] + ' lock(s): ' + [Number]

    FROM Locks

    RETURN @Result

END

GO

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

/* Execution */

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

/* Switch option ON and OFF: */

SET CURSOR_CLOSE_ON_COMMIT /* ON */ OFF

/* Uncomment ONE of the following options at a time: */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION 

-- Table Variable to hold the measurement results

DECLARE @Measurements TABLE 

(

    [Step] NVARCHAR(50)

   ,[TempDb KBytes] INT

   ,[Locks] NVARCHAR(200)

)

INSERT @Measurements

SELECT 'Transaction started' 

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) 

      ,DockOfTheBay.ufn_Locks(@@spid) 

-- Dummy variable for the FETCH operation

DECLARE @temp SQL_VARIANT

-- Declare and open a keyset-driven cursor.

DECLARE c1 CURSOR 

    /* Please uncomment ONE of the following options at a time: */

    -- DYNAMIC 

    DYNAMIC SCROLL_LOCKS 

    -- KEYSET

    -- STATIC  

FOR

    SELECT *

      FROM Production.ProductModel 

      /* Uncomment to see the impact: */

      -- WITH (NOLOCK)

     WHERE ProductModelID > 1 

     /* Uncomment to see the impact: */

     -- FOR UPDATE

OPEN c1

INSERT @Measurements

SELECT 'After Open' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

FETCH NEXT FROM c1 INTO @temp, @temp, @temp, @temp, @temp, @temp

INSERT @Measurements

SELECT 'After Fetch' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

COMMIT TRANSACTION

INSERT @Measurements

SELECT 'After Commit' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

-- Close and deallocate the cursor.

CLOSE c1

DEALLOCATE c1

INSERT @Measurements

SELECT 'After Deallocate' As [Step]

      ,DockOfTheBay.ufn_TempDb_Internal_Objects(@@spid) AS [TempDb KBytes]

      ,DockOfTheBay.ufn_Locks(@@spid) AS [Locks]

-- Display results

SELECT * FROM @Measurements

GO

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

/* Cleanup */

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

DROP FUNCTION DockOfTheBay.ufn_TempDb_Internal_Objects

DROP FUNCTION DockOfTheBay.ufn_Locks

DROP SCHEMA DockOfTheBay

GO