Decoding sp_cursor* call parameters

Posted on by
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.
 
Advertisement