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?

Solution 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.

Solution 2

“A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope.”

Reference: http://msdn.microsoft.com/en-us/library/ms188782.aspx

Solution 3

Not closing a cursor will keep locks active that it holds on the rows where it is positioned. Even after closing a reference is kept to the data structures the cursor is using though (so it can be reopened) These structures are SQL server specific (so it is not just memory space or handles or so) and depend on what the cursor is actually doing, but they will typically be temporary tables or query result sets.

Not deallocating AFAIK only has to do with performance. The aforementioned resources will remain allocated and thus have a negative effect on server performance.

allocated resources from (open or closed, but non deallocated) cursors will remain allocated until the session (or connection) is closed

Comments

[Mike]

There are few triggers with local cursor declared in a database I work with. And those are not closed and are not deallocated. Is it a bug or proper use of local cursors?

[Adir D]

Don't understand which part you consider might be a bug. I would suggest that, in all likelihood, all of those cursors could be replaced by a more efficient, set-based solution that doesn't involved cursors at all. But I would have no idea since I don't know what the triggers do.

[Mike]

Cursors are used for calling some stored procedure over every row in inserted and deleted tables probably joining it with some other tables.

[Mike]

Can you clarify for how long not deallocated cursor will hold resources? And what kinds of resources system memory / handles / etc?

[Dirk]

Updated the answer with some additional info

[Mike]

Thanks, Dirk. As Aaron noticed in his answer cursors release behavior depends on whether cursors are local or global. As it is said in MSDN local cursors will be released automatically when go out of scope. What is interesting to me is what resources will be leaked with unreleased cursors besides table locks.

[Razvan Socol]

Deallocating a cursor variable is not the same thing as deallocating a cursor. From the same page: "A DEALLOCATE @cursor_variable_name statement removes only the reference of the named variable to the cursor. "

[Eclipses]

Yes you're right but as written in the official documentation, you don't need to deallocate the variable, so is extra code that you can avoid to use.

[usr]

Do you still believe that the double-close pattern is necessary? Since this answer is popular it's probably worth it to clean that part up.

[Adir D]

@usr Why? What harm is there in issuing both CLOSE and DEALLOCATE?

[usr]

Code quality issue, no difference in execution behavior. I can also make the argument that in case of error none of those statements will run. So they can't be absolutely relied on anyway.

[Adir D]

@usr I still prefer to have them both there.

[Mitch Wheat]

See Aaron's article: sqlperformance.com/2012/09/t-sql-queries/cursor-options where he recommends "LOCAL FAST_FORWARD" (I guess he forgot to come back here and update :) )