Cursor Cost Analysis

Cursor Fundamentals

When a query is executed by an application, SQL Server returns a set of data consisting of rows. Generally, applications can’t process multiple rows together; instead, they process one row at a time by walking through the result set returned by SQL Server. This functionality is provided by a cursor, which is a mechanism to work with one row at a time out of a multirow result set.

T-SQL cursor processing usually involves the following steps:

  1. Declare the cursor to associate it with a SELECT statement and define the characteristics of the cursor.
  2. Open the cursor to access the result set returned by the SELECT statement.
  3. Retrieve a row from the cursor. Optionally, modify the row through the cursor.
  4. Once all the rows in the result set are processed, close the cursor and release the resources assigned to the cursor.

The following is an example of a server cursor processing of query results from a table:

--Associate a SELECT statement to a cursor and define the
--cursor's characteristics
USE AdventureWorks2012;
GO
SET NOCOUNT ON
DECLARE MyCursor CURSOR /*<cursor characteristics>*/
FOR
SELECT  adt.AddressTypeID,
        adt.Name,
        adt.ModifiedDate
FROM    Person.AddressType adt;

--Open the cursor to access the result set returned by the
--SELECT statement
OPEN MyCursor;

--Retrieve one row at a time from the result set returned by
--the SELECT statement
DECLARE @AddressTypeId INT,
    @Name VARCHAR(50),
    @ModifiedDate DATETIME;

FETCH NEXT FROM MyCursor INTO @AddressTypeId,@Name,@ModifiedDate;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'NAME =   ' + @Name;

--Optionally, modify the row through the cursor
        UPDATE  Person.AddressType
        SET     Name = Name + 'z'
        WHERE CURRENT OF MyCursor;

        FETCH NEXT FROM MyCursor
            INTO @AddressTypeId,@Name,@ModifiedDate;
    END

--Close the cursor and release all resources assigned to the
--cursor
CLOSE MyCursor;
DEALLOCATE MyCursor;

Cursor Cost Comparison

Default Result Set

Analyzing SQL Server Overhead with Cursors

he T-SQL cursors implemented using T-SQL statements are always executed on SQL Server because they need the SQL Server engine to process their T-SQL statements.

In most cases, you can avoid cursor operations by rewriting the functionality using SQL queries, concentrating on set-based methods of accessing the data. For example, you can rewrite the preceding stored procedure using SQL queries (instead of the cursor operations) as follows (nocursor.sql in the download):

IF (SELECT  OBJECT_ID('dbo.TotalLoss')
   ) IS NOT NULL
    DROP PROC dbo.TotalLoss;
GO
CREATE PROC dbo.TotalLoss
AS
SELECT  CASE  --Determine status based on following computation
             WHEN SUM(MoneyLostPerProduct) > 5000 THEN 'We are bankrupt!'
             ELSE 'We are safe!'
        END AS Status
FROM    (--Calculate total money lost for all discarded products
         SELECT SUM(wo.ScrappedQty * p.ListPrice) AS MoneyLostPerProduct
         FROM   Production.WorkOrder AS wo
         JOIN   Production.ScrapReason AS sr
                ON wo.ScrapReasonID = sr.ScrapReasonID
         JOIN   Production.Product AS p
                ON wo.ProductID = p.ProductID
         GROUP BY p.ProductID
        ) DiscardedProducts;
GO

Cursor Recommendations

An ineffective use of cursors can degrade the application performance by introducing extra network round-trips and load on server resources. To keep the cursor cost low, try to follow these recommendations:

  • Use set-based SQL statements over T-SQL cursors, since SQL Server is designed to work with sets of data.
  • Use the least expensive cursor.
  • When using SQL Server cursors, use the FAST FORWARD cursor type.
  • When using the API cursors implemented by ADO, OLEDB, or ODBC, use the default cursor type, which is generally referred to as the default result set.
  • When using ADO.NET, use the DataReader object.
  • Minimize impact on server resources.
  • Use a client-side cursor for API cursors.
  • Do not perform actions on the underlying tables through the cursor.
  • Always deallocate the cursor as soon as possible. This helps free resources, especially in tempdb.
  • Redesign the cursor’s SELECT statement (or the application) to return the minimum set of rows and columns.
  • Avoid T-SQL cursors entirely by rewriting the logic of the cursor as set-based statements, which are generally more efficient than cursors.
  • Use a ROWVERSION column for dynamic cursors to benefit from the efficient, version-based concurrency control instead of relying upon the value-based technique.
  • Minimize impact on tempdb.
  • Minimize resource contention in tempdb by avoiding the static and keyset-driven cursor types.
  • Static and key-set cursors put additional load on tempdb, so take that into account if you must use them, or avoid them if your tempdb is under stress.
  • Minimize blocking.
  • Use the default result set, fast-forward-only cursor, or static cursor.
  • Process all cursor rows as quickly as possible.
  • Avoid scroll locks or pessimistic locking.
  • Minimize network round-trips while using API cursors.
  • Use the CacheSize property of ADO to fetch multiple rows in one round-trip.
  • Use client-side cursors.