Checking query duration using the sys.dm_exec_requests dmv

[pmscorca]

Hi,

I've tried to use this query after the execution of the statement SELECT * FROM mytable:

SELECT 
    session_id,
    status,
    start_time,
    command,
    sql_handle,
    plan_handle,
    database_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource,
    last_wait_type,
    cpu_time,
    total_elapsed_time,
    reads,
    writes,
    logical_reads,
    text
FROM 
    sys.dm_exec_requests AS r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE 
    t.text = 'SELECT * FROM mytable';

but no rows are returned.

Perhaps the query is wrong.

Any suggests to me in order to checking the execution time of an user query? Thanks

[AndyDDC · Accepted solution]

Hi @pmscorca you could try the DMVs in the queryinsights schema as dm_exec_sql_text is not supported in Fabric Warehouse (sys.dm_exec_sql_text (Transact-SQL) - SQL Server | Microsoft Learn)

SELECT * FROM queryinsights.exec_requests_history WHERE command  = 'select * from mytable'

[Anonymous]

Hi @pmscorca ,

Any update on this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @pmscorca,

I agree with AndyDDC's point of view, use DMVs should more suitable for your requirement to check these executed query requests.

If the full query can't get expected results, you can split these query string and use fuzzy search to check these keywords. (e.g. WHERE command LIKE '%SELECT *%' and command LIKE '%mytable%')

In addition, if you remove conditions from the query, can you query data from these used tables?

Regards,

Xiaoxin Sheng

[AndyDDC · Accepted solution]

Hi @pmscorca you could try the DMVs in the queryinsights schema as dm_exec_sql_text is not supported in Fabric Warehouse (sys.dm_exec_sql_text (Transact-SQL) - SQL Server | Microsoft Learn)

SELECT * FROM queryinsights.exec_requests_history WHERE command  = 'select * from mytable'