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

1 comment:

  1. If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (even if they're dating somebody else now) you must watch this video
    right away...

    (VIDEO) Want your ex CRAWLING back to you...?

    ReplyDelete