Get query from cursor

• Mauricio_

  SSCrazy

  Points: 2935

  More actions

  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.

   

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  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.