Have you ever wondered what SQL generated that FETCH or FETCH API_CURSOR statement?

~ franklinyamamoto

Have you ever seen a FETCH statement show up when monitoring via activity monitor, sysprocesses, dm_exec_requests, or name your monitoring tool of choice?  These surface when a process is programatically fetching through a cursor.  Most of the time the fetch statement isn’t of interest when performance tuning.  Instead it is of more use to see the actual statement from the cursor that is being fetched.  In this post I’ll show how you can find the statement associated with the fetch.

sys.dm_exec_cursors

The key to finding the actually statement associated with a FETCH statement is using the DMV sys.dm_exec_cursors.  This is a table valued function that takes a session_id as an input.  You can return every cursor in every session by passing a 0 for the session_id.  In our example we’ll join to sys.dm_exec_requests on session_id instead.

This example will return sessions with open cursors.  It will not only display the sql text based on the sql_handle found in sys.dm_exec_requests, but it will show the associated declare statement used to create the cursor.

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

Some example output from this is below.  The statement_text_er column shows the statement text that is related to the sql_handle from sys.dm_exec_requests.  The statement_text_ec column shows the declaration of the statement associated with the open cursor in the session.  This is fairly representative of what you would see if declaring a cursor and iterating through it via a stored procedure or in a SQL Batch.

Below is another example of output when a cursor is declared in a program (this was declared in a COM object).  This typically shows up as FETCH API_CURSOR.

 

5 thoughts on “Have you ever wondered what SQL generated that FETCH or FETCH API_CURSOR statement?”

  1. Hi Franklin

    Thank you for the great article. I have been struggling with this issue for a while, an every time I run into the same problem.

    So I have to challenge you on this one :-). And hopefully together we can find a solution.
    Because I have tried this now, and if one session have more than one cursor declared, when you can’t say which query is behind the FETCH API_Cursor. Because as I see it, there is no link between the cursor_id and the sys.dm_exec_requests – where you find the “FETCH API ..” statement from.

    Below you will see a live example of an expanded version of your query, where you in line 2 and 3, will see the “FETCH API_CURSOR000000000001E456” with two different “statement_text_ec”.

    Please, let me know if you have any comments or questions.

    Thanks,

    Sorry, if the output is formatted badly for you …

    Expanded version of your query:

    SELECT
    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
    ,er.database_id
    ,er.session_id
    ,er.start_time
    ,ec.creation_time as cursor_created
    ,ec.cursor_id
    ,ec.is_open
    ,ec.plan_generation_num
    ,ec.fetch_buffer_size
    ,er.cpu_time
    ,er.total_elapsed_time
    ,er.reads
    ,er.logical_reads
    ,er.writes
    ,er.query_hash
    ,er.sql_handle
    ,ec.sql_handle
    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

    OUTPUT:
    statement_text_er statement_text_ec database_id session_id start_time cursor_created cursor_id is_open plan_generation_num fetch_buffer_size cpu_time total_elapsed_time reads logical_reads writes query_hash sql_handle sql_handle
    SELECT A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK FROM HZITEMDIMATTRIBUTE A,HZASSORTMENT B,HZHORZE20STOREFRONT C WHERE (A.DATAAREAID=@P1) AND (B.PRODUCTSKU=A.PRODUCTSKU) AND ((C.DATAAREAID=@P2) AND ((C.STOREFRONTID=B.STOREFRONTID) AND (C.INVENTORYLISTID=@P3))) GROUP BY A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK ORDER BY A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK SELECT A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK FROM HZITEMDIMATTRIBUTE A,HZASSORTMENT B,HZHORZE20STOREFRONT C WHERE (A.DATAAREAID=@P1) AND (B.PRODUCTSKU=A.PRODUCTSKU) AND ((C.DATAAREAID=@P2) AND ((C.STOREFRONTID=B.STOREFRONTID) AND (C.INVENTORYLISTID=@P3))) GROUP BY A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK ORDER BY A.PRODUCTSKU,A.ITEMID,A.INVENTDIMID,A.CONFIGID,A.INVENTSIZEID,A.EXTERNALSTOCK 22 89 2019-09-17 12:00:27.060 2019-09-17 12:00:27.103 181454861 0 1 0 919 1466 9804 5351 0 0xD31C40827318E469 0x020000000B48C929D59F33C3BB76AEB28AB0EA55DD6F8E1A0000000000000000000000000000000000000000 0x020000000B48C929D59F33C3BB76AEB28AB0EA55DD6F8E1A0000000000000000000000000000000000000000
    FETCH API_CURSOR000000000001E456 SELECT A.AUTOMATICTRANSACTION,A.GROUPID,A.PRIVATETASK,A.CAPTION,A.SERVERID,A.DEL_BATCHJOURNALLINERECID,A.EXECUTEDBY,A.CLASSNUMBER,A.COMPANY,A.STATUS,A.EXCEPTIONCODE,A.CONSTRAINTTYPE,A.SESSIONIDX,A.BATCHJOBID,A.IGNOREONFAIL,A.RUNTYPE,A.STARTDATETIME,A.STARTDATETIMETZID,A.RETRIESONFAILURE,A.RETRYCOUNT,A.RUNTIMETASK,A.SESSIONLOGINDATETIME,A.SESSIONLOGINDATETIMETZID,A.ENDDATETIME,A.ENDDATETIMETZID,A.MODIFIEDDATETIME,A.CREATEDDATETIME,A.DEL_CREATEDTIME,A.CREATEDBY,A.RECVERSION,A.RECID,A.PARAMETERS,A.INFO FROM BATCH A WHERE (RECID=@P1) 22 112 2019-09-17 12:00:26.977 2019-09-17 12:00:24.870 181462103 1 1 1 1527 1552 162 824910 0 NULL 0x0200000031FF311408F9F4B62824790BD75CE9589E4F9A740000000000000000000000000000000000000000 0x0200000049AD38148E5CCA874C102DD4B8D67917091FF3450000000000000000000000000000000000000000
    FETCH API_CURSOR000000000001E456 SELECT TOP 1 A.CUSTGROUP,A.REFNUM,A.SALESID,A.ORDERACCOUNT,A.INVOICEACCOUNT,A.INVOICEDATE,A.DUEDATE,A.CASHDISC,A.CASHDISCDATE,A.QTY,A.VOLUME,A.WEIGHT,A.SUMLINEDISC,A.SALESBALANCE,A.ENDDISC,A.INVOICEAMOUNT,A.CURRENCYCODE,A.EXCHRATE,A.SALESADMINISTRATOR,A.INVOICEID,A.LEDGERVOUCHER,A.UPDATED,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.DIMENSION4_,A.DIMENSION5_,A.DIMENSION6_,A.ONACCOUNTAMOUNT,A.TAXPRINTONINVOICE,A.LISTCODE,A.DEL_PRINTED,A.DOCUMENTNUM,A.DOCUMENTDATE,A.CASHDISCPERCENT,A.INTRASTATDISPATCH,A.DELIVERYNAME,A.DELIVERYADDRESS,A.PURCHASEORDER,A.DLVTERM,A.DLVMODE,A.PAYMENT,A.CASHDISCCODE,A.INVOICEROUNDOFF,A.SUMMARKUP,A.COVSTATUS,A.RETURNITEMNUM,A.POSTINGPROFILE,A.BACKORDER,A.PREPAYMENT,A.DLVZIPCODE,A.DLVCOUNTY,A.DLVCOUNTRYREGIONID,A.DLVSTATE,A.TAXGROUP,A.TAXITEMGROUP,A.DEL_TAXSPECIFYTOTAL,A.TAXSPECIFYBYLINE,A.EINVOICELINESPECIFIC,A.DEL_CORRECTEDINVOICEID,A.ONETIMECUSTOMER,A.PAYMENTSCHED,A.SUMTAX,A.SALESTYPE,A.EINVOICEACCOUNTCODE,A.INTERCOMPANYPOSTED,A.PARMID,A.RETURNREASONCODEID,A.EUSALESLIST,A.EXCHRATESECONDARY,A.TRIANGULATION,A.CUSTOMERREF,A.VATNUM,A.NUMBERSEQUENCEGROUP,A.LANGUAGEID,A.INCLTAX,A.LOG,A.PAYMDAYID,A.INVOICINGNAME,A.INVOICINGADDRESS,A.INVZIPCODE,A.INVCOUNTY,A.INVCOUNTRYREGIONID,A.INVSTATE,A.GIROTYPE,A.CONTACTPERSONID,A.SALESORIGINID,A.BILLOFLADINGID,A.INVENTLOCATIONID,A.FIXEDDUEDATE,A.DELIVERYCITY,A.DELIVERYSTREET,A.INVOICECITY,A.INVOICESTREET,A.DEL_PRINTORIGINALS,A.DEL_PRINTCOPIES,A.INVOICEAMOUNTMST,A.INVOICEROUNDOFFMST,A.SUMMARKUPMST,A.SUMLINEDISCMST,A.ENDDISCMST,A.SALESBALANCEMST,A.SUMTAXMST,A.PRINTMGMTSITEID,A.RETURNSTATUS,A.DEL_REFDLVZIPCODE,A.DEL_REFINVZIPCODE,A.INTERCOMPANYCOMPANYID,A.INTERCOMPANYPURCHID,A.PRINTEDORIGINALS,A.PROFORMA,A.RCSALESLIST_UK,A.REVERSECHARGE_UK,A.CASHDISCBASEDATE,A.CFDICREDITREFINVOICE_MX,A.RECEIPTDATECONFIRMED_ES,A.PAYMID,A.CONSIGNEEACCOUNT_RU,A.PRINTED_HU,A.VATONPAYMENT_RU,A.CORRECT_RU,A.COPIESPRINTED_HU,A.SUMTAX_W,A.EXCHRATE_W,A.WHOISAUTHOR_LT,A.INVOICESTATUS_LT,A.NUMBERSEQUENCECODE_LT,A.CORRECTEDINVOICEID_RU,A.CORRECTEDINVOICEDATE_RU,A.ECONOMICACTIVITY_LV,A.TAXINVOICESALESID,A.TRTRANSPORTATIONPROPERTIES_LT,A.INTRASTATFULFILLMENTDATE_HU,A.VATSETTLED_HU,A.TAXREIMBURSEMENTDOC_HU,A.TRLOADDATETIME_LT,A.TRLOADDATETIME_LTTZID,A.UNITEDVATINVOICE_LT,A.INVENTPROFILETYPE_RU,A.INVOICEPOSTINGTYPE_RU,A.SETTLEVOUCHER_RU,A.INVENTOWNERID_RU,A.REFORIGINALINVOICE_RU,A.DEL_NPIBANKINGPAYMID,A.CONSIGNORACCOUNT_RU,A.PRINTSTANDARDCURRENCY_RU,A.OFFSESSIONID_RU,A.NONREALREVENUE_RU,A.ATTORNEYDATE_RU,A.ATTORNEYID_RU,A.ATTORNEYISSUEDNAME_RU,A.FACTUREDFULLY_RU,A.RCONTRACTCODE,A.RCONTRACTACCOUNT,A.CUSTINVOICEPRINTOUTTYPE_RU,A.DLVTOWNID_RU,A.DLVSETTLEMENTID_RU,A.DLVSTREETID_RU,A.DLVESTATE_RU,A.DLVBUILDING_RU,A.DLVFLAT_RU,A.INVTOWNID_RU,A.INVSETTLEMENTID_RU,A.INVSTREETID_RU,A.INVESTATE_RU,A.INVBUILDING_RU,A.INVFLAT_RU,A.DEL_TRCREATEDATE_LT,A.DEL_TRCREATETIME_LT,A.DEL_TRCREATEPLACE_LT,A.TRRESPONSIBLE_LT,A.TRCARRIERTYPE_LT,A.TRCARRIERCODE_LT,A.DEL_TRTRUCKBRAND_LT,A.TRTRUCKPLATENR_LT,A.TRDRIVERNAME_LT,A.TRPACKAGE_LT,A.TRDANGERDEGREE_LT,A.DEL_TRLOADDATE_LT,A.DEL_TRLOADTIME_LT,A.TRLOADADDRNAME_LT,A.TRLOADADDRESS_LT,A.TRLOADADDRCOUNTRY_LT,A.TRLOADADDRSTATE_LT,A.CONSTANTSYMBOL_CZ,A.INTRASTATSPECMOVE_CZ,A.BANKACCOUNT_LV,A.CUSTBANKACCOUNT_LV,A.COMPANYCURBANKACCOUNT_LV,A.INBLANKLIST_LV,A.STATEINVOICEPRINTED_LV,A.INTRASTATADDVALUE_LV,A.SALESDATE_W,A.FISCALDOCSTATE_PL,A.FISCALDOCDATE_PL,A.EXPORTSALES_PL,A.EXPORTSALESVOUCHER_PL,A.EXPORTSALESDATE_PL,A.FISCALPRINTERCODE_PL,A.FISCALORDERACCOUNT_PL,A.FISCALINVOICEACCOUNT_PL,A.INVOICEREGISTER_LT,A.TRUCKTRAILERNUM_LT,A.PRINTBLANKDATE_LT,A.TRUCKMODEL_LT,A.HZSHIPCARRIERACCOUNT,A.HZSHIPCARRIERID,A.HZSPECIALFOLLOWUP,A.MODIFIEDDATETIME,A.MODIFIEDBY,A.CREATEDDATETIME,A.DEL_CREATEDTIME,A.CREATEDBY,A.RECVERSION,A.RECID,B.INTERFACEID,B.CUSTGROUPID,B.RECVERSION,B.RECID,A.DEL_CORRECTIVEREASON FROM CUSTINVOICEJOUR A,HZEDIFKPARA B WHERE ((A.DATAAREAID=@P1) AND (A.INVOICEDATE>=@P2)) AND ((B.DATAAREAID=@P3) AND ((B.CUSTGROUPID=A.CUSTGROUP) AND (B.INTERFACEID=@P4))) AND NOT EXISTS (SELECT ‘x’ FROM HZEDIBUSINESSRELATION C WHERE ((C.DATAAREAID=@P5) AND ((((C.EXTERNALACCOUNT=A.INVOICEACCOUNT) AND (C.EXTERNALACCOUNTTYPE=@P6)) AND (C.IDENTIFIER=A.INVOICEID)) AND (C.IDENTIFIERTYPE=@P7)))) ORDER BY A.DATAAREAID,A.INVOICEID,A.INVOICEDATE 22 112 2019-09-17 12:00:26.977 2019-09-17 12:00:25.237 181462127 1 1 0 1527 1552 162 824910 0 NULL 0x0200000031FF311408F9F4B62824790BD75CE9589E4F9A740000000000000000000000000000000000000000 0x02000000840F8018FBA305EE75AE25761F79443FE60CC6110000000000000000000000000000000000000000

    1. I’ll need to pull up a test case and see if I can figure out a way around that. Thanks for the feedback Jan!

  2. Thanks Franklin, this was great info. I’m really not sure why FETCH_CURSOR_API was blocking other things, since only small select statements showed in the results. Maybe there was a larger transaction in the cursor.

    1. When you say small select did you mean it only returns a few rows? I’ve seen plenty of cases where a query will do a lot of logical reads (in the millions) and only return a couple rows. When that happens blocking is a common occurrence given you are using read committed or anything more restrictive for your transaction isolation level. Thanks for reading and for the comment!

Comments are closed.