How to identify the user that ran a Query

Ensors_BH

Hello,
We have an issue occasionally where a large query is run on our application, which can use 100% of CPU on the server for an extended period, and we would like to be able to identify the user that started the query so we can better troubleshoot what causes the issue. Is this possible from SQL Monitor? We can easily see the top queries, but i cannot see how to identify the user that started the query.

Alex B

Actually, I've just realized that the "SQL User Processes" section on the server overview recently had some work done and it will list the login, host and query information.  This is from 10.1.7 or so.  You can see it in action here on our demo site: https://monitor.red-gate.com/overviews/SQLMON-EC2-BM2/cluster/sscdbcluster.ssc.local/sql/ssc-db-n1%5C(local)#?MaxTime=1598622327837&Range=6&Zoom=1598600727837%2C1598612474308

So you would want to set the zoom window on the server overview to be around the CPU spike and then it should show the SQL Processes in that section.  They should appear if they are blocking, blocked, or above a threshold cpu and in the top 10 processes by CPU or duration I believe.

jmillar

I ran into exactly the same situation today -- curious about any feedback.

Alex B

The top queries is a cumulative metric (i.e. it shows totals or average per execution rather than specific runs). 

If you click into a query and there was a specific wait associated with the query that will show the login that experienced the wait - or if you had an alert for blocking process or long-running query then it would give more details on a specific query including the user involved.

Ensors_BH

Hi Alex, that's great, thank you. I'm running 10.0.8 at the moment so I will get our monitor installation updated.