Cursors with name FETCH API_CURSOR000000000 are created by CA Service Catalog

Products

• CA Service Catalog
• CA Service Catalog

Issue/Introduction

In CA Service Catalog environment, we can see several cursors opened by CA Service Catalog. We can see this by running the following SQL Query:

select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id

Why so many cursors with name FETCH API_CURSOR000000000 are created by CA Service Catalog?
What functionality within Catalog is calling those cursor calls?
How can we lower the frequency of these SQL cursor calls?

Environment

CA Service Catalog 17.x

SQL Server

Resolution

i. Why these SQL cursor calls (FETCH API_CURSOR000000000) are sent so many times? - There are places in the application from where these are called and this is by design. There are certain calls which are generating from the framework like JPA and some explicitly from the application logic.

ii. What functionality within Catalog is calling those cursor calls (FETCH API_CURSOR000000000)? There are no specific areas which make this call. This is application wide by design.

iii. How can we lower the frequency of these SQL cursor calls(FETCH API_CURSOR000000000)? - The queries which are embedded in the cursor comes from various parts of the application.