Being Mindful of Cursor Lock Overhead

This post is just a reminder to be attentive to the locking overhead of your Transact-SQL server cursors.

For example, the following cursor is using default options in SQL Server 2012 to iterate row-by-row through the Employee table.  I’m declaring a variable and populating it with the BusinessEntityID from each row (and I’m not doing anything with it – as I just wanted to avoid the chatter back to my SQL Server Management Studio window):

   1:  DECLARE Employee_Cursor CURSOR FOR

   2:   

   3:  SELECT BusinessEntityID

   4:  FROM [HumanResources].[Employee];

   5:   

   6:  SELECT properties

   7:  FROM sys.dm_exec_cursors(52);

   8:   

   9:  OPEN Employee_Cursor;

  10:   

  11:  DECLARE @BusinessEntityID int;

  12:   

  13:  FETCH NEXT FROM Employee_Cursor

  14:  INTO @BusinessEntityID;

  15:   

  16:  WHILE @@FETCH_STATUS = 0

  17:  BEGIN

  18:     

  19:      FETCH NEXT FROM Employee_Cursor

  20:      INTO @BusinessEntityID;

  21:   

  22:  END

  23:   

  24:  CLOSE Employee_Cursor;

Now what kind of locking behavior did I see?  Using extended events and tracking lock_acquired and lock_released, I saw 2,340 events total.  Below is just a bit of what I saw – with the IS mode acquired for object, then IS mode lock for page, then S lock mode for the key, released for that key, acquired for the next key, released, and then released for the page and object and then starting all over again until reaching the final row of the table.

I also included a call to sys.dm_exec_cursors for my test session id (52).  This returned the following property value:

TSQL | Dynamic | Optimistic | Global (0)

What about using SCROLL_LOCKS? (TSQL | Dynamic | Scroll Locks | Global (0)).  Here is the slightly modified T-SQL code and abridged result set:

   1:  DECLARE Employee_Cursor  CURSOR SCROLL_LOCKS

   2:  FOR

   3:   

   4:  SELECT BusinessEntityID

   5:  FROM [HumanResources].[Employee];

   6:   

   7:  SELECT properties

   8:  FROM sys.dm_exec_cursors(55);

   9:   

  10:  OPEN Employee_Cursor;

  11:   

  12:  FETCH NEXT FROM Employee_Cursor;

  13:   

  14:  WHILE @@FETCH_STATUS = 0

  15:  BEGIN

  16:     

  17:      FETCH NEXT FROM Employee_Cursor;

  18:   

  19:  END

  20:   

  21:  CLOSE Employee_Cursor;

  22:  DEALLOCATE Employee_Cursor;

Now we see IX (object), IU (page) and U (key) modes enter the mix.  By the way, the 1237579447 is associated with the Employee table.  The 72057594045136896 value is the container_id – which is a type “1” (IN_ROW_DATA), so we can cross reference to sys.partitions.hobt_id, which then correlated to the Employee table.

Are the locks necessary? Maybe, depending on what you wish to achieve, but I find that often the cursor settings are not configured intentionally. 

As an exercise, you can check sys.dm_exec_cursor to see cursors running on your server at that moment in time.  This DMF will surface the session IDs, cursor name, properties and the associated sql_handle.  You may see settings that are unexpected or are overkill for what is actually required.