TSQL cursors vs API cursors - poor cursor usage

Cursors are based for a definitive purpose, but heavy usage of such methods will prove as costly expense on database performance. The process of cursor is prolonged, as a cursor first has to be defined with its features set, then populated after positioning (scrolled) to a set of record(s) and fetched every time. Finally once the process is completed it will be closed by releasing the resources used from SQL engine. So keeping the usage on high level this will have negative performance.

The SQL optimizer will have a query that was executing or had results pending to send to the client was considered active. In some situations, the client application might need to read through the results and submit other queries to SQL Server based on the row just read from the result set. This could not be done with a default result set, since it could have other pending results. A common solution was to change the connection properties to use a server-side cursor. Using server side is better than performing round trips to client and server for simple set of rows.

In general if you execute SP_WHO you would see the transparently encapsulated client requests inside of special extended stored procedures, such as sp_cursoropen, sp_cursorfetch, and so forth. These processess are called as API cursor as originated from client side using ODBC, OLEDB etc. Though you can handle such usage by controlling the number of rows that are fetched, it is possible for the ODBC driver or OLE DB provider to cache the row(s). This prevents a situation where the server is waiting for the client to read all the rows it has sent. Thus, the server is ready to accept a new request on that connection.

As opposed to use API cursors the TSQL cursors do not support processing blocks of records. Only one record can be fetched at a time. As the types of cursors are used for their purpsoe, by default the TSQL cursors are used from stored procedures, batches, functions, or triggers to repeat custom processing for each row of the cursor. Say you need to perform such operations using a Application that open cursors and fetch one row (or a small number of rows) at a time can easily become bottlenecked by the network latency, especially on a wide area network (WAN).  Batches or stored procedures that return multiple result sets, SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses and also you cannot use API cursors when referring to a remote stored procedure.

This is where the API cursors loses the battle due to the overhead required to process many cursor requests may become significant. Because of the overhead associated with repositioning the cursor to the appropriate location in the result set, per-request processing overhead, and similar processing, it is more efficient for the server to process a single request that returns 100 rows than to process 100 separate requests which return the same 100 rows but one row at a time.

So how you can detect to troubleshoot such poor usage of cursors. SYSMON aka Perfmon counters such as  SQL Server:Cursor Manager By Type – Cursor Requests/Sec. This will obtain the information about how many cursors are being used on the system, though there are no set or specific counters to tell you about the fetch buffer size. This is where DMV comes into picture, which is sys.dm_exec_connections:

select  cur.*  from   sys.dm_exec_connections con 
    cross apply sys.dm_exec_cursors(con.session_id) as cur 
where 
    cur.fetch_buffer_size = 1  
    and cur.properties LIKE 'API%'    

UsefulArticle on API cursors, BOL refers that: When the API cursor attributes or properties are set to 

anything other than their defaults, the SQL Native Client OLE DB provider and the SQL Native Client 

ODBC driver use API server cursors instead of default result sets. Each call to an API function that 

fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.