I am running SQL profiler to troubleshoot a long running query. I am trying to figure out a query that is taking a very long time. There are a few applications running on this server. I know I can use profiler to get the “SQL:BatchStarting” and the “SQL:BatchCompleted” to capture the TextData and find out the SQL query that is running and how long it took. My question is how can I see the current query that is running in SQL Server without having to wait until it is completed. Some of the queries are taking a very long time (may even abort at the end) and makes it difficult to troubleshoot and find out which one it was. Is there a way to run a SQL Command to see the current running queries on the server?
Hope I was clear enough.
FYI: I can do this in Oracle running the following command. Looking for the same in SQL Server.
substr(a.username,1,10) UserName,a.sid, substr(a.osuser,1,10) osuser, p.spid OS_PID, a.AUDSID, substr(a.process,1,10) Session_ID,a.serial#,a.Last_Call_ET, a.command, substr(a.lockwait,1,5) lockwait ,substr(a.machine,1,17) machine ,substr(a.program,1,10) program,b.piece,b.sql_text
v$session a, v$sqltext_with_newlines b,v$process p
a.status = 'ACTIVE' AND a.username <> 'SYS'
AND a.sql_hash_value= b.hash_value
AND a.paddr = p.addr
ORDER BY osuser,sid,piece;