SA0024 : Local cursor not closed

Cursors in T-SQL should be closed promptly to prevent unnecessary resource locking.

Description

In SQL Server, cursors allow row-by-row iteration over a result set, which can be useful for complex logic that can’t be easily achieved with set-based operations. However, if cursors remain open unnecessarily, they can hold locks on database tables or views, leading to potential performance degradation and blocking issues.

Example of a cursor that is left open:

SQL
1DECLARE cur CURSOR FOR
2SELECT * FROM TableName;
3OPEN cur;
4-- Cursor is not closed explicitly

This query leaves the cursor open, which can impact other operations. It is a best practice to close the cursor explicitly when it is no longer in use to release the resources and locks.

  • Open cursors unnecessarily hold locks, leading to blocking and increased contention.

  • Failure to close cursors can cause memory and resource leaks, adversely affecting server performance.

How to fix

Explicitly close the cursor when it is no longer needed to release resources and prevent performance issues.

Follow these steps to address the issue:

  1. Declare and open the cursor as needed in your T-SQL code using DECLARE CURSOR and OPEN statements.

  2. Use the cursor to perform the necessary row-by-row operations.

  3. Once the cursor operations are complete, explicitly close the cursor using the CLOSE statement to release any locks.

  4. Deallocate the cursor with the DEALLOCATE statement to free memory resources.

For example:

SQL
 1-- Example of a corrected query using a cursor
 2DECLARE cur CURSOR FOR
 3SELECT * FROM TableName;
 4
 5OPEN cur;
 6
 7-- Perform operations with the cursor
 8
 9CLOSE cur;
10DEALLOCATE cur;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1DECLARE objects_cursor CURSOR FOR 
2SELECT object_id, name
3FROM sys.objects where type = 'U' 
4
5OPEN objects_cursor
6
7--CLOSE objects_cursor
8
9DEALLOCATE objects_cursor

Analysis Results
  Message Line Column
1 SA0024 : Local cursor ‘objects_cursor’ not closed. 1 8
See Also

Other Resources