Detecting Poor Cursor Usage in SQL Server

Undoubtedly, poor cursor usage is a critical issue to review in order to mitigate the risk of CPU bottlenecks and to assess whether cursors are the most appropriate means for processing or if set-based operations would be more effective. It is well known that set-based operations are generally more efficient; however, if you choose to use cursors, you must ensure that they do not adversely affect the performance of the database.

Identifying poor cursor usage and implementing measures to alleviate the problem is essential. In SQL Server, various techniques can be employed to diagnose this issue. Firstly, by using performance counters, we can analyse how poor cursor usage impacts the performance of specific workloads or the entire database. For example, the performance counter SQL Server: Cursor Manager By Type: Cursor Requests/Sec provides information on the number of SQL cursor requests received by the server. Additionally, it is possible to filter by cursor manager instance, such as API Cursor (which shows only API cursor information), TSQL Global Cursor, and TSQL Local Cursor. Refer to the SQL Server: Cursor Manager by Type object for more information about this counter.

Moreover, utilising SQL Trace can also be beneficial. For instance, you can set up a trace that includes the RPC event class to search for sp_cursorfetch statements. The fourth parameter value indicates the number of rows returned by the fetch. It is important to note that the maximum number of rows requested is specified as an input parameter in the corresponding RPC

event class. Finally, by using the DMV sys.dm_exec_cursors, we can determine whether poor cursor usage exists on the database server, as Transact-SQL cursors always have a fetch buffer of 1, while API cursors should have a higher value.

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

Consequently, if it is observed that API Cursors have a fetch buffer size of 1, consider enabling Multiple Active Results Sets (MARS) when connecting to SQL Server. Consult the appropriate documentation for your specific API to determine how to specify a higher fetch buffer size for the cursor, whether using ODBC (SQL_ATTR_ROW_ARRAY_SIZE) or OLE DB (IRowset::GetNextRows, IRowsetLocate::GetRowsAt). Once you have done this, you can retrieve more details about the session and connections of the users associated with the problematic cursors to determine the appropriate course of action.

select s.session_id, cn.client_net_address, s.login_name, s.status,s.client_interface_name, s.program_name, 
       c.cursor_id, c.name, c.properties, c.plan_generation_num, c.creation_time, c.is_open, c.fetch_status, 
       c.fetch_buffer_size, c.worker_time, c.reads, c.writes, c.dormant_duration
from sys.dm_exec_connections cn
inner join  sys.dm_exec_sessions s on cn.session_id = s.session_id
cross apply sys.dm_exec_cursors(s.session_id) as c
where c.fetch_buffer_size = 1 
     and c.properties LIKE 'API%'

That is all for now, thanks for reading. Let me know any remarks you may have. Stay tuned.