Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 ) 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 ):
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.