What happens when you forget to close and deallocate cursor?

Question

Leaving cursor open is known as a bad practice. But what really happens when you forget to close and/or deallocate it? How does it affect SQL Server, connection/session? Are there any differences in consequences for queries, stored procedures and triggers using cursors?

Answer 1

It depends on whether you declared the cursor locally or globally (and what the default is in your environment - default is global but you can change it).

If the cursor is global, then it can stay "alive" in SQL Server until the last piece of code is touched in the scope in which it was created. For example if you call a stored procedure that creates a global cursor, then call 20 other stored procedures, the cursor will live on while those other 20 stored procedures are running, until the caller goes out of scope. I believe it will stay alive at the session level, not the connection level, but haven't tested this thoroughly. If the cursor is declared as local, then it should only stay in scope for the current object (but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm).

The general concept, though, should be: when you're done with something, say so.

In order to make my cursors as efficient as possible, I always use the following declarations:

DECLARE c CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
  FOR SELECT ...

I've also heard that there can be memory issues if you only CLOSE or only DEALLOCATE so I always do both when I'm done:

CLOSE c;
DEALLOCATE c;

However how many cursors do you have where cleaning up this syntax is an issue? If you have hundreds of cursors in your system, that is certainly a red flag to me.

EDIT

As an addendum, I just want to clarify that cursors in and of themselves are not bad. They are often misused and abused, though - implemented in cases where a more efficient, set-based solution could have been implemented, but the person tasked with writing the query could only think procedurally. A few cases where cursors make sense:

• Running totals. In my testing, cursors obliterate all pre-SQL 2012 set-based solutions (at least those that are documented and supported0.
• Various administrative tasks, e.g. call a stored procedure for every row in a table or for every table or database.
• When the set-based alternative is extraordinarily complex, or the task is a one-off.