INF: Understanding Worktables Used by Server Side Cursors

• Microsoft SQL Server, version 6.5

SUMMARY

SQL Server implementation of server side cursor uses worktables to maintain keyset and optimistic concurrency values. This article describes the format and contents of these worktables in SQL Server version 6.5. You can find additional information on server side cursors in the SQL Server documentation and the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q156489
   TITLE     : INF: Overview of SQL Server, ODBC, and DB-Library Cursors

   ARTICLE-ID: Q132037
   TITLE     : INF: Locking Behavior of Cursors on SQL Server Version 6.0

MORE INFORMATION

For each table involved in each type of cursor, one or two worktables are created in tempdb at cursor declaration/open time. One is the keyset table that stores the row identifier (RID) and the key values (and also possibly the timestamp). The other is the OPTCCVAL table, which stores the complete rows that are currently in the fetch buffer.

The following section describes these worktables in more detail, based on the type of cursor is used.

KEYSET-DRIVEN

When the cursor is opened, the query defining the cursor is run and the RID and unique key values of the qualifying rows are saved in a keyset table in tempdb. There is one such keyset table for every table involved in the query. If there is a timestamp on the table, it is also saved in the keyset table. All further cursor fetch commands are based on these keyset tables. To fetch a given row, first a RID lookup is performed on the main table, based on the saved RID value in the keyset table. If the row is found, the key values (or timestamp if it exists) are compared with those that are saved in the keyset table. If these match, the desired columns are fetched from this row. Otherwise, an index-based scan is performed on the table, searching for the row with the given unique key values found in the keyset table. These keyset tables are created during cursor declaration, and populated during cursor open. Closing and reopening a cursor causes these tables to be truncated and repopulated. For a cursor with a very large results set, the task of populating these keyset tables may take up a considerable amount of time during cursor open. This problem can be solved in two ways: by using dynamic cursors, or by populating keyset tables asynchronously.

Format of keyset tables: row# + RID + key values (+ timestamp if it exists)

If the cursor is opened with an optimistic concurrency option of OPTCC or OPTCCVAL and there is no scroll locking in effect, for every table that is involved in the query with no timestamp, there will also be an OPTCCVAL table that stores the complete rows corresponding to the currently fetched buffer. When an optimistic lookup is needed later, the values of the current row are compared against the values in the OPTCCVAL table row. This requires truncation and re-population of the OPTCCVAL table(s) with every fetch command, which can introduce negative impact on cursor performance. Users and application developers concerned with obtaining maximum performance can avoid an OPTCCVAL cursor type by using either timestamp or scroll locking instead. Scroll locking can be achieved either by using the LOCKCC option on the cursor, or by using the UPDLOCK or HOLDLOCK keywords in the original query. Because LOCKCC, UPDLOCK, and HOLDLOCK will likely reduce concurrency and possibly introduce more blocking in the system, it is recommended that you carefully evaluate performance and concurrency requirements, and choose the proper cursor type accordingly.

DYNAMIC

The keyset tables are populated only during cursor fetch operations. These tables only contain the RIDs and key values of the rows that were in the last fetched buffer. Every fetch operation truncates the keyset table and refills it with the new qualifying rows. This eliminates the problem of a cursor open taking too long for a large results set, while adding extra overhead and a performance penalty to fetch operations. In cases when the tempdb is too small to accommodate huge keyset tables, this may be the only option.

The keyset table may have one of the following forms:

• If the cursor is opened with an optimistic concurrency option of OPTCC or OPTCCVAL:

  1. Table has timestamp and unique index: row#+RID+keys+timestamp

  2. Table has timpstamp but no unique index: row#+RID+timestamp

  3. Table has no timestamp: row#+RID+complete row

• If the cursor is opened with an optimistic concurrency option of Read-Only or LOCKCC:

  1. Table has unique index: row#+RID+keys

  2. Table has no unique index: complete row

FORWARD-ONLY

In SQL Server 6.5, FORWARD-ONLY cursors are DYNAMIC by default, but if the query plan involves interim worktables, the FORWARD-ONLY cursor is opened as KEYSET-DRIVEN if there is a unique index on every one of its underlying tables, and INSENSITIVE and READ-ONLY otherwise. In addition, if the Transact-SQL statement contains one of the keywords UNION, DISTINCT, GROUP BY and/or HAVING, or aggregate functions, the cursor is automatically READ- ONLY and INSENSITIVE.

As an additional performance improvement, FORWARD-ONLY DYNAMIC ANSI cursors have a procedure cache page instead of a temporary table to store the keyset because these cursors are DYNAMIC and only have one row in the fetch buffer at a time.

INSENSITIVE (STATIC)

No keyset or OPTCCVAL table is created; a temporary table is created and the complete result set is copied into the temporary table at cursor open time.