How track where query originates from?

Brian Maday 1 Reputation point
2021-02-05T13:21:13.797+00:00

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.

Azure SQL Database
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Nasreen Akter 10,761 Reputation points
    2021-02-06T02:06:51.163+00:00

    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!

    64746-sqldb.jpg

    ----------

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

    0 comments No comments

  2. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-02-08T07:42:17.187+00:00

    @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.

    0 comments No comments