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) |
![]() ![]() |
||
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.
[…] Cursor Statistics Are Missing in dm_exec_query_stats – Michael J. Swart (Blog|Twitter) […]
Pingback by (SFTW) SQL Server Links 22/01/16 - John Sansom — January 22, 2016 @ 3:01 am
[…] Michael Swart notes that cursor performance can be hidden from monitoring tools: […]
Pingback by Cursors: The Hidden Performance Problem – Curated SQL — January 22, 2016 @ 8:01 am
Hello Michael,
Great work, as always!! Love the explanations and examples.
Rudy 🙂
Comment by Rudy Panigas — January 26, 2016 @ 9:12 am
Thanks Rudy!
Comment by Michael J. Swart — January 26, 2016 @ 4:24 pm
Hi Michael,
We have an application that uses cursors, and this blog clears up some questions I had for measuring performance.
Thanks for sharing this!
Comment by Ger — January 30, 2016 @ 5:19 am
Great FETCH reference. One other place to get OPEN CURSOR statistics would be extended events.
Comment by Jason Strate — February 16, 2016 @ 2:12 pm
Thanks Jason,
I briefly mentioned extended events, but I never dove into it because I’ve never actually tried it out.
Thanks again for the feedback.
Comment by Michael J. Swart — February 16, 2016 @ 2:16 pm
Oops, missed that. That’s what I get for reading on a Monday.
Comment by Jason Strate — February 16, 2016 @ 2:55 pm
Nice to learn this. Thank you, Michael!
Comment by Denis Reznik — February 18, 2016 @ 7:36 am
[…] Cursor Statistics Are Missing in dm_exec_query_stats: «Когда кто-то пишет курсор, где-то далеко, в страшных муках умирает маленький котёнок» © Алексей Ковалёв. Что да, то да, курсоры в сиквеле — это практически чистое зло. И ещё один злостный момент, связанный с ними, — dm_exec_query_stats не трекает запрос курсора. Подробнее в статье. […]
Pingback by SQL Server Дайджест #9: SQLSaturday Kyiv, механизмы защиты данных в БД, инструменты для параллельного запуска запросов - LIKELY.CLUB — November 9, 2016 @ 11:50 am