SQL Cursors and TempDB

During a meeting yesterday the question was asked "Aren't cursors stored in tempDB?"  My initial response was no they will be rendered in memory but the correct answer is..."It depends!" Let me preface the rest of this blog post by saying when possible use set-based operations rather than cursors as cursors may be a hit on performance, but as in all things SQL it is best to test.

According to Books Online, Insensitive/Static cursors create a temporary copy of the data being accessed by the cursor in TempDB.  Likewise a Keyset cursor will build a keyset containing the uniquely identifiying columns for the cursor in TempDB.  Other cursors Fast_Forward, Forward_Only, and Dynamic do not incur the overhead associated with storing all of the cursor data in TempDB.  By using the dmv, sys.dm_session_space_usage, I was able to view the page allocations in TempDB using the different cursor types.  I noticed that there were page allocations to TempDB no matter which type of cursor was chosen but there were far more page allocations for a Keyset cursor  versus a Fast_Forward or Static cursor.

When programming cursors I typically declare a cursor as Fast_Forward which creates a forward only static cursor.  This option performs better than other cursor options and seems to work well for me as I rarely have to move backwards through a cursor or modify the cursor data.  If I am updating or deleting data through a cursor then I will use a Forward_Only, Dynamic cursor.  I often have seen developers use the ISO syntax when creating cursors such as follows.

1:  DECLARE cur_DBs CURSOR  
2:  FOR  
3:  SELECT Name  
4:  FROM sys.databases d  
5:  WHERE d.state_desc = 'ONLINE';  

By omitting the T-SQL cursor model in the declaration statement a Keyset, optimistic, non-scrollable cursor is created which will have its keyset materialized in TempDB.

So there you have it.  Cursors will have some page allocations in TempDB but how many will depend on the type of cursor being declared.  When declaring cursors Fast_Forward cursors have less overhead and impact on TempDB.  So when reviewing or writing code remember to declare the type of cursor being created to reduce overhead.