How to get more details(user, execution time, IP address) for a given query ID on Azure SQL DB

Question

How can I get all the details(user, execution time, IP address, etc) for a given Query ID? I am using Azure SQL DB

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

You can use a combination of system views and functions.

SELECT 
    q.query_id,
    qt.query_sql_text,
    qp.plan_id,
    qrs.last_execution_time,
    qrs.count_executions,
    qrs.avg_duration,
    qrs.avg_cpu_time,
    qrs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats qrs ON qp.plan_id = qrs.plan_id
WHERE q.query_id = YOUR_QUERY_ID;

For more detailed information, including the user and client IP address, you can combine this with other system views:

SELECT 
    q.query_id,
    qt.query_sql_text,
    s.login_name AS [User],
    c.client_net_address AS [IP Address],
    qrs.last_execution_time,
    qrs.count_executions,
    qrs.avg_duration,
    qrs.avg_cpu_time,
    qrs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
JOIN sys.query_store_runtime_stats qrs ON qp.plan_id = qrs.plan_id
OUTER APPLY sys.dm_exec_sql_text(q.last_compile_batch_sql_handle) AS st
LEFT JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
WHERE q.query_id = YOUR_QUERY_ID;

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful