Background on this example

This is a fairly simple example, but is from a real world stored procedure.  The procedure accumulates the special instructions for all tasks assigned to a department and outputs them as a single string.  In this application, each job is assigned multiple tasks required to complete the job and each of those tasks has its own row in the JobTaskData table.  Production departments complete a portion of those tasks as the job moves from start to finish through production.  More than one task may be completed by each department.

Originally the procedure used a cursor with no options to produce the output (as did most of the several hundred stored procedures in our database).   When I took this job, one of the first things I did to improve performance on our server was to modify the stored procedures to add the LOCAL and FAST_FORWARD options to the cursors.   This listing is the Cursor version of the SP modified only enough to allow it to be run interactively in a new query window in SSMS:

WHILE @@FETCH_STATUS = 0
      BEGIN
            SET @hDeptTaskInstructions = ' ' + @hDeptTaskInstructions + ' ' + @hTaskInstructions
            IF @hDeptTaskInstructions is null
            Begin
                  Set @hDeptTaskInstructions = ' '
          End
            FETCH NEXT FROM TaskInstructions_cursor INTO @hTaskInstructions
      END
CLOSE TaskInstructions_cursor
DEALLOCATE TaskInstructions_cursor
select @hTaskInstructions

The same query without the LOCAL and FAST_FORWARD options produced five iterations of a much less efficient plan.  

It’s pretty obvious that the LOCAL and FAST_FORWARD options on cursors provide a less resource intensive execution.  But you can also see the difference in the client statistics when the queries are executed.  Trial 4 has the Local and Fast_Forward options set on the cursor, while trials 3 and 5 had those keywords removed.  The Local and Fast_Forward options cut the number of I/O packets and the total bytes sent from the server in half!

An Even Better Option, Eliminate the Cursor!

Most SQL Server experts will tell you that eliminating cursors altogether and using set operations to produce similar output is the best way to write queries in SQL server.    I thought I would put that to the test with this fairly simple example.

declare @hQuote_ID as Char(9) = 'Q14-10000'
declare @hVersionNo as Char(3) = '022'
declare @hDepartment_ID as Int = 2
declare @hDeptTaskInstructions as Varchar(500) = ' '
select @hDeptTaskInstructions =  @hDeptTaskInstructions + coalesce(JobTaskData.TaskInstructions+ ' ', '')
      FROM  JobTaskData INNER JOIN Task ON JobTaskData.Task_ID = Task.Task_ID
      WHERE (JobTaskData.Quote_ID = @hQuote_ID) AND
                  (JobTaskData.VersionNo = @hVersionNo) AND
                  (Task.Department_ID = @hDepartment_ID) AND
                  (Task.TaskTypeID = 1)        
select @hDeptTaskInstructions

This may or may not be the most efficient way of combining row data into a single string, but it does get the job done while eliminating the cursor.  This query produces the same results as the original query but with even less load on the server.  The execution plan is a single iteration of the select and uses my indexes as expected. 

That doesn’t look too different that the Local Fast_Forward cursor’s plan except that it’s only executed once, but the real benefit is seen in the Client Stats.

There are fewer Select statements (7 v 20 before) for this version, and the Network stats show a significant cut with both the number of packets sent and the number of bytes sent. 

Conclusions

This example is a simple one. There are only a handful of rows returned that match this query, but still the improvements are obvious.  I've seen much more dramatic improvemts when replacing more complex (and often convoluted) stored procedures with set based logic, even when I've used table variables or temp tables for intermediate results.   

The standard advice given by most SQL Server experts holds true.  If you can, eliminate cursors from your SQL Server environment.  This change will likely greatly reduce the server resources required to complete your query.  If you MUST use cursors and if your cursors are accessed the way most are (locally and only with the Fetch Next syntax) at least use the Local and Fast_Forward modifiers on them. 

I've been rewriting these stored procedures whenever the chance (and time) arrises.  Usually the chance comes when making a minor change to functionality or providing a bug fix.   If you are also afflicted with a hoard of stored procedures that use cursors, I'd suggest it's worth a little bit of time to modify them to add the Local and Fast_Forward keywords to any cursors that are only accessed with a Fetch Next command.  Such a change takes only a minute or 2 per stored procedure.  If you have a need to make a functional change to the stored procedure (or if your SP is one of the more actively used on your system), it's well worth a bit of extra time to rewrite the procedure using set based queries instead of cursors.  Your system will be glad you did.

One last bit.  If you don't know which procedures in your database are using cursors, you can quickly find out using a query like:

SELECT Object_name(object_id) as [Procedure Name],
       Object_definition(object_id) as [Definition]
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%cursor%'
Order by [Procedure Name]