Post reply

Get query from cursor

  • June 11, 2019 at 8:53 am

    #3652164

    Hi,

    I have a program developed with a tool that generates cursors everywhere. If I run a query to get the most expensive queries regarding logical reads, for example, I've got something like this:

    (No column name)                                                     execution_count total_logical_reads last_logical_reads

    FETCH API_CURSOR00000000002E4433       5940                       165362023                 78609

    FETCH API_CURSOR00000000002CD9D7      1121                         50132767                   24219

    FETCH API_CURSOR00000000002F1E84       733                          25853048                  49040

    How can I get the query text of the cursor? Is there any way to identify what those cursors are? Ideally, with some dmvs, I know that with extended events I can probably get them but I wonder if there is another way.

    Thanks in advance.

     

  • June 11, 2019 at 9:37 pm

    #3652510

    Something like this:

    SELECT
           c.session_id
          ,es.program_name
          ,es.login_name
          ,es.host_name
          ,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;

    Source: https://community.dynamics.com/ax/f/33/t/194602

    --Vadim R.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply