Every now and then, one encounters a legacy application that uses the API Server Cursors(see http://msdn.microsoft.com/en-us/library/ms187088.aspx) or Cursor Stored Procedures http://msdn.microsoft.com/en-us/library/ms187801.aspx.
In Profiler or Trace, these calls involve sp_cursoropen and sp_cursorfetch, or sp_prepare/sp_prepexec and sp_execute. (This is not the same as cursors declared in SQL statements). API Server Cursors are sometimes blamed for performance problems. This could be generic poor performance or a nearly complete SQL Server system seizure with memory leak symptoms.
The general poor performance is usually due to the way API Server Cursors are used. In most situations, I have seen sp_cursorfetch calls that get 1 row at time, with hundreds or thousands of calls to get all rows. So to understand this, I chased down the documentation to cursor parameter values (see the SSWUG article) I was surprised to find that the API Server cursors were fully capable of fetching multiple rows with a single call. The problem was that the client side elected to fetching only one row at a time.
The other possible problem associated with API server cursors is execessive network round-trips. When the query returns only a single row, the API cursors may generate 3 calls, sp_cursoropen to setup the cursor, sp_cursorfetch to get the row, and sp_cursorclose to release resources used by the cursor. (The ODBC call that does this should also release resources on the client.)
The cost of the network roundtrip overhead from client to SQL Server can be 6-8 times higher than a single row index seek. So generating 3 network calls when 1 was sufficient is a seriously bad technique. Generating thousands of call when only 1 (with many network packers) was necessary is even worse. Furthermore, scaling network roundtrips on big server systems, especially NUMA, requires special handling, with possible serious negative consequences if not done correctly.
The number of network round-trips required can be reduced from 3 to 2 with sp_prepexec, and sp_unprepare. The first procedure both prepares a statement and executes for the first parameter. The handle can be used for additional executes with different parameter values.
The second more serious problem, occurs when the client neglects to close the cursor or prepared statement. So if the profiler trace shows calls to the sp_cursoropen and sp_cursorfetch without a call to sp_cursorclose or calls to sp_prepare/sp_prepexec and sp_execute without a call to sp_unprepare, then the client side code never called SQLFreeStmt (the next syntax is SQLCursorClose or SQLFreeHandle?).
In this case, the cursor or prepare statement is still accessible, meaning SQL Server will not release resources necessary to access it, even though the client will never access it again. This is essentially a memory leak. On 32-bit SQL Server, I have seen SQL Server lock up with several million open cursors (this probably depends on the resources consumed by each cursor or prepared statement). Presumably 64-bit SQL Server can go quite a while before exhausting the virtual address space.
In the past on SQL Server 2000, I have also seen this happen when one cursor is left open, followed by a very long stream (millions) of cursors openned and closed. This may sound silly, but consider a Visual Basic program with a nest while loop. The outer loop opens a cursor, executes its, but leaves it open. Then inner loop opens a cursor, and executes it. The code does not close the cursor explicitly (SqlCmd=nothing?). As the code goes to the next iteration of the inner loop, a new cursor is openned for the current iteration, then VB garbage collector kicks in, releasing resources created in the previous iteration, which are nolonger accessible.
At PASS last week, I asked one of the Microsoft SQL Programmability reps about this subject. He was under the impression that it was possible to close API Server cursors from a different session. I am not sure how this is done.
If anyone still has application that issues API Server Cursors, and experiences these problems, first I would strongly suggest rewriting the entire application to not use the API Server Cursors. Second choice would be to fix the code. In the case that this was a third-party product, and they are unresponsive, perhaps the Microsoft SQL Server team would be so kind as to provide a means to close open cursors in other sessions. Perhaps sp_reset_connection could be slipped in somehow?
ps It is common practice in performance tuning to run a Profiler or Trace with filters to avoid overwhelming the system. The sp_cursorclose and sp_unprepare almost always have zero CPU and duration (no sure on logical reads). So a filtered trace will not show these. If there are API Server Cursor calls, it could be helpful to run an filtered trace on just the RPC:Completed event, or filter to a specific spid.