Michael J. Swart

The dmv dm_exec_query_stats doesn’t track stats for OPEN CURSOR statements. This is a problem because the OPEN statement is the one that “runs” your query and if you rely on these stats to monitor performance, then cursor performance is hidden from you.

Cursors have a bad reputation, probably well-deserved. When I see a cursor, I see someone trying to use SQL as a programming language. It’s not what SQL is good at and there’s often a better way.

The pragmatist in me doesn’t care too much. If a cursor is performing well and not causing too much trouble, then fixing will not be a priority. But my monitoring solution doesn’t show me how expensive those cursors are! I realize I have no idea what my cursors are doing or how expensive they are.

Cursor Statements

Developers use a number of SQL Statements when writing cursors: DECLARE, OPEN and FETCH. Performance-wise, the DECLARE CURSOR statement takes no time. The OPEN statement runs the query and puts the results in a temporary table. And the FETCH statement reads the next row from the table.

If a cursor’s query is untuned, it’s the OPEN statement that consumes the most resources.

Example

The OPEN statement is missing from sys.dm_exec_query_stats. I want to demonstrate that. Run the following on a dev box.

-- fresh start:
DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET NOCOUNT ON;
GO
 
-- declare a cursor with an arbitrary query that reads a little bit
DECLARE @ChecksumValue int;
 
print 'declare cursor:'
DECLARE FiveRows CURSOR LOCAL FAST_FORWARD FOR
  SELECT TOP 5 CHECKSUM(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  ORDER BY CHECKSUM(*)
 
-- this statement actually runs the query that was just declared
print 'open cursor:'
OPEN FiveRows
 
-- fetch the five rows one at a time
DECLARE @i INT = 0;
WHILE( @i < 5 )
BEGIN
    print 'fetch cursor:'
    FETCH NEXT FROM FiveRows INTO @ChecksumValue
 
    SET @i += 1;
END
 
CLOSE FiveRows
DEALLOCATE FiveRows;
 
GO
 
-- Now look at dm_exec_query_text to see what's in there
SELECT 
    qs.query_hash as QueryHash,
    qs.total_logical_reads + total_logical_writes as TotalIO,
    qs.execution_count as Executions,
    SUBSTRING(
        st.[text],
        qs.statement_start_offset / 2,
        (qs.statement_end_offset - qs.statement_start_offset) / 2
        ) as SQLText    
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.[sql_handle]) st
ORDER BY qs.total_logical_reads + total_logical_writes DESC
OPTION (RECOMPILE)

The results of that last query show that the OPEN statement is missing from dm_exec_query_stats:

And the messages tab shows that the OPEN statement did in fact read from tables.

declare cursor:
open cursor:
Table 'Worktable'. Scan count 0, logical reads 21, ...
Table 'syscolpars'. Scan count 1, logical reads 15, ...
Table 'sysschobjs'. Scan count 1, logical reads 38, ...
Table 'sysscalartypes'. Scan count 1, logical reads 2, ...
fetch cursor:
Table 'Worktable'. Scan count 0, logical reads 2, ...
fetch cursor:
Table 'Worktable'. Scan count 0, logical reads 2, ...
fetch cursor:
Table 'Worktable'. Scan count 0, logical reads 2, ...
fetch cursor:
Table 'Worktable'. Scan count 0, logical reads 2, ...
fetch cursor:
Table 'Worktable'. Scan count 0, logical reads 2, ...


Workarounds

If your cursors are defined inside a procedure, you can inspect dm_exec_procedure_stats. This is not an option when cursors are run as ad-hoc SQL (outside a procedure). Remember that you’ll only get the performance numbers for the entire execution of the procedure. This view doesn’t tell you which statements inside the procedures are expensive.

There’s good news if your monitoring solution is based on extended events or SQL Trace. You’ll be able to monitor cursors correctly.

If you plan to use Query Store, the new feature in SQL Server 2016, then you’ll be able to see statistics for the OPEN query. Query Store doesn’t store statistics for the DECLARE statement. But that’s acceptable because DECLARE statement don’t use any resources.

Summary

Use the following to keep everything straight.

	DECLARE CURSOR	OPEN CURSOR	FETCH CURSOR
dm_exec_query_stats			(queryhash = 0x0)
dm_exec_procedure_stats (if run as sproc)	(aggregated)
SQL Trace (e.g. Profiler)
SET STATISTICS IO ON
Show Plan	*
Query Store (2016)	*

* This is acceptable because we don’t care about the performance of DECLARE statements.