Cursors and memory scalability in SQL Server 2005

Cursor implementation in SQL Server 2005 is redesigned for improved memory usage. This post takes a peek at the internals of the SQL Server 2005 cursor implementation. Most of the discussion below is geared towards server cursors as used by drivers like SQLNCLI or ODBC, even though ANSI cursors use the same implementation underneath.

Cacheable memory state in the server, e.g a procedure cache entry, can be released if not currently in use, under memory pressure. Unlike procedure cache entries, a cursor needs to carry memory state which is not reclaimable even when there are no batches currently running in the system that uses the particular cursor. This means that there is a limit on the number of cursors the system can support meaningfully. In SQL Server 2000 and earlier releases, some of the ISV applications had run into this limitation. SQL 2005 has improved the design in such way as to make large part of memory that cursors use reclaimable as well as sharable among different cursor instances using a particular plan.

The memory state used by the cursor can be classified roughly into three groups:

1.
2. Compiled plan

3. which holds the queries implementing the cursoring

4. Run-time cursor state (e.g current position within rowset)

5. Run-time memory used by query scan operators

In SQL Server 2000, all of the memory above was not reclaimable when a cursor is kept open. The crux of the re-design in SQL Server 2005 was to make the third group (query scan operator memory) independent of the second group (run-time cursor state). In order to do this, we built the cursor plans in such a way that, run-time query scan memory is re-generated on demand, using the cached plan as the seed. In fact, all the query statements are implemented this way, so in reality, cursor queries are just brought inline with other statements like SELECT and DML. Further, this automatically reduces memory profile of cursors since query scan operator memory is sharable among different cursors that use the same plan. The cursor plans themselves are not re-generatable in SQL 2005 and hence we must keep the cursor plan around while the cursor is active. However, plan is sharable among cursors which use the same cursor declare batch, which makes this not a problem for a well-written app with good plan sharing. Run-time cursor state is local to the user’s session and is neither sharable nor recoverable when the cursor is active.

The plan used by the cursor is built when the DECLARE batch is compiled (for server cursors used by drivers, this is the batch corresponding to sp_cursoropen RPC that you will find in the profiler trace log). Cursor plans, in general, are comprised of two independent queries – one for OPEN and one for FETCH (exception: dynamic cursors doesn’t have an open query). Both these query plans are tucked under the DECLARE statement in the compiled plan. The compiled plan is cached in the procedure cache like any other plan. The execution of the DECLARE statement creates a cursor and stores it in the user’s session.

Since most of the compilation work is done under the declare batch, OPEN and FETCH compilations are fast. Only minimal work need to be done for OPEN and FETCH statement compiles. Subsequently the statement memory associated with OPEN and FETCH statements are also minimal.

Execution of OPEN and FETCH does the following:

1. Locate the cursor in the session.
2. Locate the cursor plan (remember that plans are not released under any circumstances when the cursor is open).
3. From the plan we build an appropriate query scan operator tree.
4. Execute the query.
5. At the end of the query this tree is cached along with the OPEN and FETCH statements, away from the cursor. A different cursor with the same cursor plan can re-use this tree with after a re-initialization step.
6. Further executions will simply re-use the cached tree after appropriate plan version validation.

Finally, to make things perform better when we have repeated declare/use/deallocate patterns, we do have a way to cache run-time cursor allocations (the second group in the list above) globally after user deallocates the cursor. The deallocated cursors are reclaimable under memory pressure along with plans used by those cursors.

Figuring out the cursor state thru DMVs

There are several SQL Server 2005 DMVs that might help you monitor or investigate the cursor state. I will list some techniques below which might be handy. Note that there are might be other routes that get you the same set of information.

A useful first step is to out the sql handle for the DECLARE statements. This can be obtained from any number of ways sql handle is exposed. For simplicity let us go to sys.dm_exec_cursors (check out this DMV, it has bunch of useful information on cursors):

   select sql_handle from sys.dm_exec_cursors (@@spid)

Second step

will be to figure out the plan handle for the cursor DECLARE statement:

   select p.plan_handle
from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_plan_attributes(p.plan_handle) pa
where
pa.attribute = 'sql_handle' AND pa.value = <sql handle from step 1>

Now with the plan handle, you could examine the DECLARE cursor plan (which embeds OPEN and FETCH queries) using sys.dm_exec_query_plan. These plans are helpful in understanding of the cursor query implementations (e.g differences between static and keyset fetch plans):

         select * from sys.dm_exec_query_plan(<plan_handle from step 2>).

Now let us figure out the memory objects that that are associated with each of the three groups of memory we identified above. First group is the plan memory:

      select memory_object_address from sys.dm_exec_cached_plans where plan_handle = <plan_handle from step 2>

Second group is the cursor state memory objects. We can go directly to the sys.dm_os_memory_objects DMV to figure this out. We have one memory object of type 'MEMOBJ_CURSOREXEC' per cursor. Note that it is harder to tie this to an individual cursor. Also, this list will include both cached deactivated cursors and active cursors. On the positive side, aggregation of total server memory allocated to this group can be easily calculated.

      select memory_object_address from sys.dm_os_memory_objects where type = 'MEMOBJ_CURSOREXEC'

The third group, run-time query scan memory, is associated with the batches that execute the OPEN and FETCH statements. These can be tracked using the memory object type MEMOBJ_XSTMT, like any other query statements.

Comments

• Anonymous
  August 14, 2008
  I know this is an old post, but I am interested in learning more about whether improvements to cursors in SQL 2005 / 2008 mean that Transact-SQL cursors should not be treated as outcasts to the extent that they so often are.  For example, is it really better / more efficient to have a while loop popping rows off of a temp table one at a time, without using an explicitly-declared T-SQL cursor, as opposed to using a firehose cursor, letting the cursor manage the temporary storage, and so forth?  I have found myself more and more ostracized for defending the use of T-SQL cursors in some cases, and am wondering whether I am all wrong about this, or if the whole community is basically obsessed with needlessly treating cursors as evil. Can anyone comment?  Especially anybody on the SQL team?

• Anonymous
  August 14, 2008
  As always, processing one row at a time should be avoided whenever possible. The main run-time difference between cursors and firehose resulset is that each cursor fetch involves equivalent of a query startup/finish.  Unfortunately this is true in 2005/2008 as well. In addition, cursors tend to hold on to resources. We reduced the memory profile of the cursors in 2005, as this article indicates, but there is still some resource cost associated with cursors. Investigate using other mechanisms like cross apply and CLR as well before making a choice.

• Anonymous
  August 15, 2008
  The comment has been removed

• Anonymous
  June 15, 2009
  PingBack from http://edebtsettlementprogram.info/story.php?id=22864