How to identify the user that ran a Query
Ensors_BH
Posts: 2 New member
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.
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.
Tagged:
Best Answer
-
Alex B Posts: 1,153 Diamond 4Actually, 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.
Answers
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.
Have you visited our Help Center?