Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 )

Question

I have a strange situation. Using sp_whoisactive I can see this:

Ok, with this query, I can see what is triggering ( does this word exists in english? ) it:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (SPID) c --0 for all cursors running
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

the result:

it's a simple select. Why is this using fetch_cursor?

Also, I see a lot of "blank" sql_texts too. Does this has something with this "cursor"?

DBCC INPUTBUFFER (spid) shows me this:

there's this question Here ( made by me ) but i don't know if this is the same thing.

EDIT1:

Using the query provided by kin, I see this:

EDIT2:

Using Activity Monitor, I can see this:

It is the most expensive query ( The first one is intentional, we know about it ).

And again, I would like to know, why this select * from... is the reason of FETCH CURSOR...

EDIT3:

This "select * from..." is running from another server ( via linked server ).

Well, Now i'm having problems to understand what @kin said.

This is the execution plan of the query ( running in the same server of the database):

this is now, the execution plan, running in the other server, via linked server:

Ok, Not a problem too. And NOW! the execution plan , via **activity monitor** ( the same select * from ):

Paul White · Answer 1 · 2015-08-19T12:04:40.993

It's a simple select. Why is this using fetch_cursor?

The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found.

The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls.

I believe the cursor plan you show in your question is the internal cursor opened by the engine as part of this process, but I have not had time to try to reproduce this yet.

score 1 · Answer 2 · edited May 13 '20 at 18:51

This may be an issue with OLEDB calls to remote servers (linked servers and SSIS configurations use OLEDB).

This is a design flaw, Microsoft SQL Server bug that wasn't patched until SQL Server 2012 SP1 from what I recall where it doesn't allow remote stats to be used to optimize the query remotely.

You will need to run sp_WhoIsActive (download / docs) from the REMOTE server in the query as well to see the traffic, but SQL Server that's not 2012 SP1 doesn't allow the use of remote statistics for some reason even if the login has datareader access to all tables on the remote server.

The Microsoft solution is to grant the linked server credential making the remote call to have SA, or ddladmin, or DBO access to the remote server/table(s) being queried.

I used this to workaround this issue in some of our setups which is transparent for the most part solution wise without allowing elevated permissions to DBs or SQL Servers on the remote side. You basically need to grant the remote login ddladmin role on the remote SQL Server DB in question, and then create a role with explicit DENY permissions for the object level changes if you only intend to allow SELECT access.

Below is the copy of the custom DB fixed role I create for this but you may want to test and confirm or adjust further plus some reading and research but resolved transparently for me in some instances -- the cache may need cleared though before it works so keep this in mind and once it's cleared, run it twice and check both local activity and remote activity for results.

So allow the credential the ddladmin role on the remote DB, you allow the other usual permissions on the remote DB, you create the custom DB role as I listed below on this same server, and then you add that same credential to that new custom fixed DB role with the explicit denies, clear the cache, run the query twice or more after clearing the cache to see if it resolves.

To specifically answer your question though for the reason you are seeing these cursor fetches, if you're running a version below SQL Server 2012 SP1 and seeing this and you're running a remote query, because it doesn't allow the usage or remote statistics in this setup without a workaround (as listed above), then it does the row-by-row processing as Kin stated above since the query is not optimized using stats for the best query plan and has the cardinality issue.

/* 
CREATE A NEW ROLE - Deny explicit DB object access for linked 
server credentials that the DDLAdmin role gives which is needed 
for DBCC SHOW_STATISTICS across linked servers  
*/
-- Database specific
CREATE ROLE db_LinkedServer_Restriction
DENY ALTER ANY ASSEMBLY                    TO db_LinkedServer_Restriction
DENY ALTER ANY ASYMMETRIC KEY              TO db_LinkedServer_Restriction
DENY ALTER ANY CERTIFICATE                 TO db_LinkedServer_Restriction
DENY ALTER ANY CONTRACT                    TO db_LinkedServer_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER        TO db_LinkedServer_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_LinkedServer_Restriction
DENY ALTER ANY DATASPACE                   TO db_LinkedServer_Restriction
DENY ALTER ANY FULLTEXT CATALOG            TO db_LinkedServer_Restriction
DENY ALTER ANY MESSAGE TYPE                TO db_LinkedServer_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING      TO db_LinkedServer_Restriction
DENY ALTER ANY ROUTE                       TO db_LinkedServer_Restriction
DENY ALTER ANY SCHEMA                      TO db_LinkedServer_Restriction
DENY ALTER ANY SERVICE                     TO db_LinkedServer_Restriction
DENY ALTER ANY SYMMETRIC KEY               TO db_LinkedServer_Restriction
DENY CHECKPOINT                            TO db_LinkedServer_Restriction
DENY CREATE AGGREGATE                      TO db_LinkedServer_Restriction
DENY CREATE DEFAULT                        TO db_LinkedServer_Restriction
DENY CREATE FUNCTION                       TO db_LinkedServer_Restriction
DENY CREATE PROCEDURE                      TO db_LinkedServer_Restriction
DENY CREATE QUEUE                          TO db_LinkedServer_Restriction
DENY CREATE RULE                           TO db_LinkedServer_Restriction
DENY CREATE SYNONYM                        TO db_LinkedServer_Restriction
DENY CREATE TABLE                          TO db_LinkedServer_Restriction
DENY CREATE TYPE                           TO db_LinkedServer_Restriction
DENY CREATE VIEW                           TO db_LinkedServer_Restriction
DENY CREATE XML SCHEMA COLLECTION          TO db_LinkedServer_Restriction
DENY REFERENCES                            TO db_LinkedServer_Restriction

GO

score 1 · Accepted Answer · answered Aug 19 '15 at 11:58

1

Well...We solved the problem. There was an Update, inside the procedure running that "select * from...". I commented the update. no more problems.

answered Aug 19 '15 at 11:58

Racer SQL

7,546
16
77
140

score -2 · Answer 4 · edited Jul 12 '24 at 21:01

you can use any of these queries to troubleshoot

SELECT
        er.sql_handle,
        ec.sql_handle,
         SUBSTRING(ers.text, (er.statement_start_offset/2)+1,
                     ((CASE er.statement_end_offset
                     WHEN -1 THEN DATALENGTH(ers.text)
                     ELSE er.statement_end_offset
                     END - er.statement_start_offset)/2) + 1) AS statement_text_er,
                     SUBSTRING(ecs.text, (ec.statement_start_offset/2)+1,
                     ((CASE ec.statement_end_offset
                     WHEN -1 THEN DATALENGTH(ecs.text)
                     ELSE ec.statement_end_offset
                     END - ec.statement_start_offset)/2) + 1) AS statement_text_ec
 FROM sys.dm_exec_requests er cross apply sys.dm_exec_cursors(er.session_id) ec
 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) ers
 CROSS APPLY sys.dm_exec_sql_text(ec.sql_handle) ecs

-- OR --

SELECT
        c.session_id,
        es.program_name,
        es.login_name,
        es.host_name,
        DB_NAME(es.database_id) AS DatabaseName,
        c.properties,
        c.creation_time,
        c.is_open,
        t.text
FROM sys.dm_exec_cursors (0) c
LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

-- OR --

SELECT
        creation_time,
        cursor_id,
        c.session_id,
        c.properties,
        c.creation_time,
        c.is_open,
        SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
                            ( CASE c.statement_end_offset
                            WHEN -1 THEN DATALENGTH(st.TEXT)
                            ELSE c.statement_end_offset
                            END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO

Also, you might want to check your applications connection string. e.g. JDBC connection string - look at SelectMethod, if its CURSOR then it will show up as FETCH API.... so to change the behavior change the SelectMethod=Select. If its Docker image then it will be in Database_URL

jdbc:sqlserver://<servername>\\<instance name>:10501;databaseName=Controls;integratedSecurity=true;authenticationScheme=JavaKerberos;SelectMethod=cursor

Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 )

4 Answers4

Linked