The Mystery of the Slow FAST_FORWARD Cursor in SQL Server

When Users Wait Half a Minute to Open a Transaction Detail

In a large enterprise ERP system, users in warehouses and at headquarters began reporting significant delays when opening inventory transaction details. While forms for regular items opened immediately, loading details for key items with extensive transaction histories took 25 to 30 seconds, sometimes reaching up to 90 seconds during peak periods.

An analysis of active database queries revealed that the application was accessing data through a server-side cursor. For one problematic item, the query returned more than 550,000 rows and generated over 22 million logical reads each time the form was opened.

Under the Hood: Why Did the FAST_FORWARD Cursor Fail?

The cursor was declared with the FAST_FORWARD option, which SQL Server documentation describes as a fast, forward-only, read-only cursor type.

Many developers rely on the following rule from SQL Server Books Online (BOL):

“SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.”

When SQL Server implicitly converts a cursor to a different declared type, the TYPE_WARNING option can report that conversion. However, choosing between the static and dynamic implementations of a FAST_FORWARD cursor does not constitute such a conversion. In this case, no cursor type conversion occurred.

The execution plan XML confirmed that SQL Server preserved the requested cursor type:

Figure 1. Original execution plan XML showing that both the requested and actual cursor types remained FastForward.

Both CursorRequestedType and CursorActualType are reported as FastForward. The plan also confirms that the cursor is read-only and forward-only.

This is an important distinction. Although SQL Server selected a static internal implementation for this query, the cursor itself remained a FAST_FORWARD cursor. Therefore, no cursor type conversion warning was generated, and TYPE_WARNING would not have exposed the performance issue.

SQL Server can internally implement a FAST_FORWARD cursor in two ways:

• DYNAMIC implementation: Data is not materialized in advance. The OPEN statement only initializes the cursor, taking a fraction of a millisecond. Rows are then retrieved directly from the underlying tables through individual FETCH NEXT calls.
• STATIC implementation: All qualifying rows must be fully filtered, sorted, and written to a temporary Cursor Work Table (CWT) in tempdb during the OPEN operation. Until the final row has been written, the application must wait and cannot execute even the first FETCH.

Because both implementations are valid for a FAST_FORWARD cursor, SQL Server does not issue a cursor type conversion warning when choosing between them.

The optimizer selects an execution strategy based on the query and the available execution plans. A dynamic cursor implementation must use operators compatible with dynamic cursor execution, while operations such as a blocking Sort may prevent a particular plan from being implemented dynamically.

In this case, the required sorting operation contributed to SQL Server choosing a static implementation, which materialized the result set in a Cursor Work Table (CWT) in tempdb before the first row could be fetched.

This was not a cursor type conversion. It was a choice between two valid internal implementations of FAST_FORWARD.

The execution plan for the first FETCH NEXT provides additional evidence of this behavior.

Figure 2. The FETCH NEXT execution plan accessing the cursor work table through CWT_PrimaryKey.

Instead of retrieving the next row directly from the underlying transaction tables, the FETCH NEXT operation accessed the already materialized cursor work table through CWT_PrimaryKey.

This explains why the first fetch itself was fast, while opening the cursor required so much time.

Why Did the Optimizer Choose to Write Data to tempdb?

The original query generated by the ERP application included an ORDER BY clause referencing two joined tables:

ORDER BY H.TransactionCode DESC,

         L.TransactionDate ASC

Where:

• The TransactionCode column originates from the transaction header table (TransactionHeaders), which is referenced in the query using the alias H.
• The TransactionDate column originates from the transaction line table (InventoryTransactions), which is referenced in the query using the alias L.

Because the required sort order depended on columns from two different tables, the available indexes could not provide the complete ordering needed by the query.

As a result, the execution plan required an explicit Sort operation. In the observed execution plan, this prevented the optimizer from using a suitable dynamic cursor implementation and contributed to the selection of a static execution strategy.

SQL Server therefore materialized more than 550,000 rows in a Cursor Work Table in tempdb during the OPEN operation.

Figure 3. The original cursor execution plan showing the expensive OPEN operation, explicit sorting, and cursor work table materialization.

The captured execution plan illustrates the cost of this approach. In the recorded execution, the OPEN operation took approximately 90 seconds, consumed more than 46 seconds of CPU time, and performed nearly 22 million logical reads.

The plan also shows the explicit Sort operator and the Clustered Index Insert into the cursor work table.

The important distinction is that a Sort operator does not universally guarantee a static FAST_FORWARD implementation. In this particular case, the combination of the required ordering, available indexes, and resulting execution plan led to that behavior.

Why Other Attempts Failed

Indexed/Materialized View: The query used CROSS JOIN and OUTER JOIN operations, which are not supported in SQL Server indexed views.

Removing ORDER BY: From a technical perspective, this worked perfectly. The OPEN duration dropped to 4 ms with only 63 logical reads. However, the ERP business requirements and user interface strictly required records to be displayed from newest to oldest.

Forcing a DYNAMIC Cursor: Without changing the sorting logic, this only shifted the workload. Instead of one long OPEN operation, each individual FETCH NEXT took several seconds.

A Data-Driven Solution: A Pragmatic Compromise in Sorting

The objective was to design a sort order in which all columns in the ORDER BY clause came from a single table (InventoryTransactions), allowing a supporting index to be created.

Original sorting:

ORDER BY H.TransactionCode DESC, L.TransactionDate ASC

Proposed sorting:

ORDER BY L.HeaderReferenceId DESC, L.TransactionDate DESC

Because the business stakeholders were concerned about changes to the record order in the user interface, our team performed a comparative T-SQL test on real data using the DENSE_RANK() function:

WITH OrderComparison AS (

    SELECT

        L.ProductCode,

        DENSE_RANK() OVER (

            PARTITION BY L.ProductCode

            ORDER BY H.TransactionCode DESC,

                     L.TransactionDate ASC

        ) AS OriginalRank,

        DENSE_RANK() OVER (

            PARTITION BY L.ProductCode

            ORDER BY L.HeaderReferenceId DESC,

                     L.TransactionDate DESC

        ) AS NewRank

    FROM TransactionHeaders H

    CROSS JOIN InventoryTransactions L

    WHERE H.TenantId = 1001

      AND H.CompanyCode = N'CORP'

      AND L.TenantId = 1001

      AND L.CompanyCode = N'CORP'

      AND H.HeaderId = L.HeaderReferenceId

      AND H.ProductCode = L.ProductCode

)

SELECT

    ProductCode,

    SUM(CASE WHEN OriginalRank = NewRank THEN 1 ELSE 0 END) AS ExactMatchRows,

    SUM(CASE WHEN ABS(OriginalRank - NewRank) = 1 THEN 1 ELSE 0 END) AS ShiftedByOneRow,

    SUM(CASE WHEN ABS(OriginalRank - NewRank) > 1 THEN 1 ELSE 0 END) AS ReorderedRows

FROM OrderComparison

GROUP BY ProductCode;

Note: The SQL example has been anonymized to protect customer-specific information. Table and column names are illustrative, while the query structure and comparison logic reflect the original investigation.

Test results: About 80-90% of records retained exactly the same ranking or shifted by only one position, particularly where dates were identical.

The business stakeholders confirmed that such a minor difference would be imperceptible to users and approved the sorting change.

Implementation and the Power of a Backward Scan

The query sorting was modified in the ERP application, and a supporting nonclustered index was deployed in the database:

CREATE NONCLUSTERED INDEX

    [IX_InventoryTransactions_ProductSort]

ON [dbo].[InventoryTransactions]

(

    [ProductCode] ASC,

    [CompanyCode] ASC,

    [TenantId] ASC,

    [HeaderReferenceId] ASC,

    [TransactionDate] ASC

);

Practical tip: This ERP framework does not allow descending (DESC) index columns to be defined directly in its development layer. However, this is not necessarily a problem.

SQL Server can efficiently use an index defined with ascending (ASC) columns to satisfy descending sort requirements through an Index Seek with a Backward Scan, reading the B-tree in the opposite direction.

Results: Before vs. After

Key Lessons for Database Specialists

• FAST_FORWARD does not necessarily mean a non-materialized cursor. SQL Server can choose either a dynamic or a static internal implementation. When the required execution plan contains operators incompatible with dynamic cursor execution, the optimizer may select a static implementation and materialize the result set in tempdb before the first FETCH.
• Avoid ORDER BY clauses spanning multiple tables in cursor queries where possible. Using sorting columns from a single table can allow index-based ordering.
• Validate the impact of changes with measurable results. Rather than guessing whether a slight change in record ordering will be acceptable to the business, use analytical functions such as DENSE_RANK() to compare the results. Quantifiable differences make approval easier.
• An ascending index can also support descending access. SQL Server can scan an index in both directions using a Backward Scan, so creating duplicate descending indexes is not always necessary.

SMT Stories Takeaway

A FAST_FORWARD cursor is not guaranteed to avoid data materialization. Understanding its actual execution strategy and aligning the sort order with a supporting index can turn a lengthy cursor initialization into an almost instantaneous operation.