GROC (Getting Rid Of Cursors) part three: Cursor Details

This is the third part of the "GROC-files". I will show you how to enumerate the details about the cursors in the scope of a sql module. You can use this information to figure out if these cursors can or should be optimized.

If you already have a reference (e.g. via a variable or a parameter) to cursors in the current scope, then it suffices to call sp_describe_cursor, and probably sp_describe_cursor_columns and sp_describe_cursor_tables. If you don't have a reference (e.g. within a stored procedure that is called from the body of a cursor) then you can just call sp_cursor_list. Well ... 'just call' may be an understatement. All these system stored procedures use a cursor as OUTPUT variable, so there's some work involved. In a sense I find it cruel that in order to see details about cursors you are forced to create yet another cursor. But anyway, here's how you can find out if there are cursors hanging around in your current scope:

USE AdventureWorks
GO
 
/************************/
/* Using sp_cursor_list */
/************************/
 
-- More info about the returned columns:
-- http://msdn.microsoft.com/en-us/library/ms186256.aspx
 
-- Declare and open a keyset-driven cursor.
DECLARE c1 CURSOR GLOBAL DYNAMIC
FOR
  SELECT LastName
    FROM Person.Contact
   WHERE LastName LIKE 'S%'
ORDER BY LastName
 
OPEN c1
 
-- Declare a cursor variable to hold the cursor output variable
-- from sp_cursor_list.
DECLARE @Report CURSOR
 
-- Execute sp_cursor_list into the cursor variable.
EXEC master.dbo.sp_cursor_list 
  @cursor_return = @Report OUTPUT,
  @cursor_scope = 3 -- Local and Global
 
-- Fetch all the rows from the sp_cursor_list output cursor.
FETCH NEXT from @Report
 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END
 
-- Close and deallocate the cursor from sp_cursor_list.
CLOSE @Report
DEALLOCATE @Report
GO
 
-- Close and deallocate the original cursor.
CLOSE c1
DEALLOCATE c1
GO

The result of the call to sp_cursor_list is not very intuitive:


I can easily imagine that you would prefer an output like this:


You can achieve this by writing a wrapper procedure -or table valued function- around the system stored procedure. Here's an example:

/**********************************/
/*                                */
/* User-friendly Cursor List Demo */
/*                                */
/**********************************/ 
 
USE AdventureWorks
GO
 
/***************/
/* Preparation */
/***************/ 
 
CREATE SCHEMA DockOfTheBay
GO
 
/*******************************************/
/* User-friendly version of sp_cursor_list */
/*******************************************/
 
CREATE PROC DockOfTheBay.usp_cursor_list
AS
 
SET NOCOUNT ON 
 
-- Build a table variable to hold the results from sp_cursor_list
DECLARE @Cursors TABLE
   reference_name char(30), 
   cursor_name char(30), 
   cursor_scope tinyint,
   [status] int
   model tinyint, 
   concurrency tinyint, 
   scrollable tinyint, 
   open_status tinyint, 
   cursor_rows decimal(10,0), 
   fetch_status smallint,
   column_count smallint, 
   row_count decimal(10,0),    
   last_operation tinyint, 
   cursor_handle  int
)
 
-- Declare a cursor variable to hold the cursor output variable
-- from sp_cursor_list.
DECLARE @Report CURSOR
 
-- Declare variables to hold the fetched values from @Report
DECLARE
   @reference_name char(30), 
   @cursor_name char(30),
   @cursor_scope tinyint,
   @status int
   @model tinyint, 
   @concurrency tinyint, 
   @scrollable tinyint, 
   @open_status tinyint, 
   @cursor_rows decimal(10,0), 
   @fetch_status smallint,
   @column_count smallint, 
   @row_count decimal(10,0),    
   @last_operation tinyint, 
   @cursor_handle  int
 
-- Execute sp_cursor_list into the cursor variable.
EXEC master.dbo.sp_cursor_list 
     @cursor_return = @Report OUTPUT,
     @cursor_scope = 3
 
-- Fetch the first row from the sp_cursor_list output cursor.
FETCH NEXT FROM @Report 
INTO
   @reference_name, 
   @cursor_name,
   @cursor_scope,
   @status,  
   @model, 
   @concurrency , 
   @scrollable , 
   @open_status, 
   @cursor_rows, 
   @fetch_status,
   @column_count, 
   @row_count,    
   @last_operation, 
   @cursor_handle
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- Insert the values into the temp table
   INSERT INTO @Cursors
   VALUES ( 
           @reference_name, 
           @cursor_name,
           @cursor_scope,
           @status,  
           @model, 
           @concurrency , 
           @scrollable , 
           @open_status, 
           @cursor_rows, 
           @fetch_status,
           @column_count, 
           @row_count,    
           @last_operation, 
           @cursor_handle
          )
 
   -- Fetch subsequent rows from @Report
   FETCH NEXT FROM @Report
   INTO
      @reference_name, 
      @cursor_name,
      @cursor_scope,
      @status,  
      @model, 
      @concurrency , 
      @scrollable , 
      @open_status, 
      @cursor_rows, 
      @fetch_status,
      @column_count, 
      @row_count,    
      @last_operation, 
      @cursor_handle
END
 
-- Close and deallocate the cursor from sp_cursor_list.
CLOSE @Report
DEALLOCATE @Report
 
-- Return the information from the table variable, translated into readable descriptions
IF (SELECT count(*) FROM @Cursors) > 0
  SELECT 
   [Cursor Reference] = reference_name, 
   [Declared Name] = cursor_name, 
   [Scope] = CASE cursor_scope
     WHEN 1 THEN 'local'
     ELSE 'global'
   END,
   [Status] = CASE [status]
     WHEN THEN 'open'
     WHEN THEN 'no rows'
     WHEN -1 THEN 'closed'
     WHEN -2 THEN 'no reference'
     WHEN -3 THEN 'does not exist'
   END,
   [Model] =  CASE model
     WHEN 1 THEN 'static'
     WHEN 2 THEN 'keyset'
     WHEN 3 THEN 'dynamic'
     WHEN 4 THEN 'fast forward'
   END,
   [Locking] = CASE concurrency 
     WHEN 1 THEN 'read only'
     WHEN 2 THEN 'scroll locks'
     WHEN 3 THEN 'optimistic'
   END,
   [Scrolling] = CASE scrollable 
     WHEN 0 THEN 'forward only'
     WHEN 1 THEN 'scrollable'
   END,
   [Open] = CASE open_status 
     WHEN 0 THEN 'no'
     WHEN 1 THEN 'yes'
   END,
   [Qualifying Rows] =  cursor_rows,
   [Fetch Status] = CASE fetch_status 
     WHEN THEN 'successful'
     WHEN -1 THEN 'out of bounds'
     WHEN -2 THEN 'missing row'
     WHEN -9 THEN 'no fetch done'
   END,
   [Columns] = column_count, 
   [Rows for last operation] = row_count,  
   [Last operation] = CASE last_operation 
     WHEN 0 THEN 'none'
     WHEN 1 THEN 'OPEN'
     WHEN 2 THEN 'FETCH'
     WHEN 3 THEN 'INSERT'
     WHEN 4 THEN 'UPDATE'
     WHEN 5 THEN 'DELETE'
     WHEN 6 THEN 'CLOSE'
     WHEN 7 THEN 'DEALLOCATE'
   END,
   [Handle] = cursor_handle
  FROM @Cursors
GO
 
/*********/
/* Usage */
/*********/
 
-- Declare and open a keyset-driven cursor.
DECLARE c1 CURSOR DYNAMIC GLOBAL
FOR
  SELECT LastName
    FROM Person.Contact
   WHERE LastName LIKE 'S%'
ORDER BY LastName
 
OPEN c1
 
-- Call the user-friendly version.
exec DockOfTheBay.usp_cursor_list
 
-- Close and deallocate the original cursor.
CLOSE c1
DEALLOCATE c1
GO
 
/***********/
/* Cleanup */
/***********/
 
DROP PROC DockOfTheBay.usp_cursor_list
DROP SCHEMA DockOfTheBay
GO

No comments:

Post a Comment