What happens when you forget to close and dealloca

问题:

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?

回答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.

回答2:

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

回答3:

“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