Cursor coding horrors


Most coders have stereotypes of one kind or another. Given an opportunity, the developer will give into their stereotype. The coding guidelines is what keeps them from using it. I personally have a coding stereotype in T-SQL when defining a loop. Cursors are one of the most commonly used coding constructs. However, I tend to stick to my trusted friend a WHILE loop. I have worked on multiple issues where the T-SQL developer got the next FETCH wrong due to a oversight. Let me demonstrate that with an example. I have a T-SQL sample code which prints out all the user sessions connect to the SQL instance. 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

where session_id > 50 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 

begin 

print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 

end 

close cur_sysprocesses 

deallocate cur_sysprocesses

I have the next FETCH inside the while loop and the loop is governed by the value of @@FETCH_STATUS. Since, I have defined it as NOT EQUAL TO –1, the cursor will end when the next fetch is unsuccessful. This is a perfectly alright. Not all cursors in a production environment are that simple. Some of them have levels of nesting and WHILE loops which have deep levels of nesting along with conditional blocks. So, let me modify the example and show you: 

declare @session_id int 

declare cur_sysprocesses cursor for 

select session_id from sys.dm_exec_requests 

open cur_sysprocesses 

fetch next from cur_sysprocesses into @session_id 

while @@FETCH_STATUS <> -1 

begin 

print @session_id 

if (@session_id > 50) 

fetch next from cur_sysprocesses into @session_id 

end 

close cur_sysprocesses 

deallocate cur_sysprocesses

Now the problem with the above cursor is that most system processes have a session id below 50. So, now my cursor will loop through infinitely because the next fetch will never occur since the check in the conditional block will never evaluate to true as the first fetch from the cursor will always result in a system spid lesser than 50. This seems like a really amateur mistake which you think is not possible. But believe me, this happens! Since, I am showing this to you with a common system DMV, you think this is not possible. However, when you are using user defined tables and cursors, this is quite possible. Never define your next fetch from the cursor within the WHILE loop in a conditional block which is dependant on the data being fetched from the table. 

Always have the next FETCH defined in the first level of the while loop. Never ever put this in a conditional block. Always find an alternative way to find out a workaround or a different way to decide on whether to process the data fetched from the cursor. The next FETCH from the cursor should NOT be defined in an IF conditional block. 

It’s a human being that is coding the cursor. There is always a chance of some unforeseen circumstance (un-thought-of scenario) which makes your cursor go into a doomed state (read: infinite loop). 

You can never be dependant on your data to decide the fate of your code path. 

To summarize, the next FETCH while your looping through your cursor should be in the first level of your code unless and until you have no other choice and want to avoid an infinite loop scenario.

Advertisements

Decoding sp_cursor* call parameters


I have seen multiple occasions while troubleshooting performance cases, that people get baffled with the sp_cursor* call parameters specified
 
You can capture the sp_cursor* calls with the parameters if you capture SP:Starting and SP:Completed or RPC:Starting and RPC:Completed events in a SQL Profiler Trace.
 
The different stored procedures which are related to cursors in SQL Server are:
 
  • sp_cursor: Update a cursor
  • sp_cursorclose: Close a cursor
  • sp_cursorexecute: Open a prepared cursor
  • sp_cursorfetch: Fetch rows
  • sp_cursoropen: Open a cursor
  • sp_cursoroption: Set cursor options
  • sp_cursorprepare: Prepare a cursor statement
  • sp_cursorprepexec: Prepare a cursor statement and open
  • sp_cursorunprepare: Free a prepared cursor statement
  • sp_execute: Execute a prepared statement
  • sp_prepare: Prepare an SQL statement
  • sp_prepexec: Prepare and execute an SQL statement
  • sp_unprepare: Free a prepared statement
Scroll Options – Is the cursor scroll type. scroll_options is of integer data type with a default of 1 (keyset-driven), and can be a combination of these values ("exactly one of the first 5 must be specified").

Value Description
0x0001 Keyset-driven cursor.
0x0002 Dynamic cursor.
0x0004 Forward-only cursor.

0x0008 Static cursor.
0x0010 Fast forward-only cursor.
0x1000 Parameterized query.
0x2000 Auto fetch.
0x4000 Auto close.
0x8000 Check acceptable types.
0x10000 Keyset-driven acceptable.
0x20000 Dynamic acceptable.
0x40000 Forward-only acceptable.
0x80000 Static acceptable.
0x100000 Fast forward-only acceptable.
 
Concurrency Options – Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic) and can be a combination of these values (exactly one of the first 4 must be specified). On return, @ccopt contains the type of cursor actually created, which may not match what was requested.

Value Description
0x0001 Read-only.
0x0002 Scroll locks.
0x0004 Optimistic. Checks timestamps and, when not available, values.
0x0008 Optimistic. Checks values (non-text, non-image).
0x2000 Open on any SQL.
0x4000 Update keyset in place.
0x10000 Read-only acceptable.
0x20000 Locks acceptable.
0x40000 Optimistic acceptable.

 
How to identify the correct values for scrollopt:
For Example: If the cursor call for sp_prepexec is like this:
declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=1
 
@P1 is the STATEMENT HANDLE
@P2 is the CURSOR HANDLE
@P3 is the PARAMETER DEFINITION LIST
@P4 is the STATEMENT
@P5 is the SCROLL OPTIONS
@P6 is the CONCURRENCY OPTIONS
@P7 is the ROW COUNT i.e. the variable declared to receive the number of affected rows
 
So if @P5 = 28688
Then, Decimal 28688 = Hex 7010 = 0x4000+0x2000+0x1000+0x0010 which translates to:
Fast Forward Only Cursor
Parameterized Query
Auto Fetch
Auto Close
 
Similarly, for ccopt values, if @P6 = 8193, then Decimal 8193 = Hex 2001 = 0x2000 + 0x0001 which translates to:
Read-Only
Open on any SQL
 
Now, the reason why I was stressing on the Completed events in the Profiler Trace was to help in identifying the Cursor Statement Handle. If the above example, the sp_prepexec that was shown was Starting event due to which @P1 parameter value was NULL. When you capture the RPC: Completed event, you will find that the Text Data column in the Profiler for the cursor call has @P1 populated. This will give you the statement handle. You can use this statement handle to track down any other statements that are executing this statement.
 
This can be quite helpful when you are chasing down the Top CPU consumer or Long Running Cursor statements as the only execution calls that you would see would be sp_cursorfetch or sp_execute along with the cursor handle. Using the Handle Value, you can track back in the profiler and find out what was the query that the cursor was prepared for.