THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

API Server Cursors and related system Cursor stored procedures

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.

Published Friday, November 19, 2010 1:07 PM by jchang
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Joe,

If you are doing one row at a time, using prepared statements is much faster as the statements are parameterized and avoid excessive (re)compilation. But yes, row at a time is a major design flaw and so it's best of the worst in this case.

BTW, a line break or two in your post would make it a lot easier to read.

November 19, 2010 1:14 PM
 

jchang said:

on the line breaks, I copied from Word, and your site preserves the Word formatting. So I removed this.

on prepared statements. Let me add: it is totally stupid to call the sequence 1) prepare, 2) execute, 3) unprepare, ie, the statment is only executed once.

Prepared statements only make sense if there are many executes, followed by the unprepare at the end. The theory is that a handle (4 byte integer) is pass to SQL Server, instead of a stored procedure (string). The 4 byte integer is much more compact and a quicker look up than the string. But considering the full network round-trip consumes on the order of 125,000+ CPU-cycles, the extra cost of the string lookup is trivial. I suspect the prepared stmt also forces the plan to be kept in cache, but I am not sure on this.

Still, I think it is better to use stored procedures (making sure stored proc calls are owner qualified and case correct). The only time prepared statements have an advantage in theory is in the case that you screwed up application architecture in the first place, so don't architect around excessive single row fetches.

November 19, 2010 1:29 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement