9

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

Strange

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:

its just a select

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"?

blank

DBCC INPUTBUFFER (spid) shows me this:

print

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:

still  no code.


EDIT2:

Using Activity Monitor, I can see this:

Mos expensive query

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):

same server of the database

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

enter image description here

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

what the hell is going on here?

2

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.

1

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.