Cursor Cost Analysis
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:
- Declare the cursor to associate it with a SELECT statement and define the characteristics of the cursor.
- Open the cursor to access the result set returned by the SELECT statement.
- Retrieve a row from the cursor. Optionally, modify the row through the cursor.
- 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
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.