How track where query originates from?

Question

Sorry if this is a simple question, but I'm a novice when it comes to Azure SQL DB. I can see a query_id that is using a lot of CPU, I'd like to be able to find the login and hostname that is running this query, is this possible? I can't find any way to link the query_id to the connections or sessions.

Answer 1

Hi @Brian Maday ,

In Azure SQL Database, to get that information, go to the left menu Activity log >> Operation Name >> select any operation >> Summary + JSON (please see the screenshot attached). Thanks!

----------

If the above response is helpful, please accept as answer and up-vote it. Thanks!

Answer 2

@Brian Maday Welcome to Microsoft Q&A forums and thank you for your interest in Azure SQL Database.

When you are observing a CPU spike on your database, you can run the below query to get the top 10 CPU consuming queries and their session ID's

PRINT '--top 10 Active CPU Consuming Queries by sessions--';  
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text  
FROM sys.dm_exec_requests AS req  
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST  
ORDER BY cpu_time DESC;  
GO  

Substitute the sessionID obtained from the above query in the one below to get the user details.

SELECT  
    c.session_id, c.net_transport, c.encrypt_option,  
    s.status,  
    c.auth_scheme, s.host_name, s.program_name,  
    s.client_interface_name, s.login_name, s.nt_domain,  
    s.nt_user_name, s.original_login_name, c.connect_time,  
    s.login_time  
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s  
    ON c.session_id = s.session_id  
--WHERE c.session_id = <YOUR SESSIONID HERE>;  
--WHERE status = 'sleeping'  
ORDER BY c.connect_time ASC  

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.