How to trace a query in progress ? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to trace a query in progress ?

Hi, I would like to have informations about a running query, like :
* what is the sql statement in progress ?
* what is the method to access to the data (table scan, index scan, …) ?
* The number of I/O and the cpu time ? Example:
I have a stored procedure composed of two SELECT: the first statement use a table scan and the second an index scan.
The sp is executed. During the execution of the sp, i would like to know what is
the sql statement in progress (for example, the second SELECT), what is the current operation (index scan), and the number of I/O + CPU Time elasped for the statement in progress. Is it possible or not ! DBCC INPUTBUFFER and ::fn_get_sql doesn’t help me much really… Thanks a lot. (sorry for my poor english) Regards;

You can trace this with Profiler. An application installed when you install SQL Server. Check in your "start menu".
No, i don’t think. Profiler can’t trace a query which is in progress. Trace is possible only if the profiler is started before executing the query. Thanks; Stephane

How about: Select cmd from MASTER..SYSPROCESSES ? Luis Martin
Moderator One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
and if you wants to monitor / look for active transaction :
dbcc opentran Regards
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

As Luis suggested, Select spid, cmd, cpu, physical_io from MASTER..SYSPROCESSES will tell you the most of the information you specified. If you want to know the exact statement for a spid, use
DBCC inputbuffer (spid)

Have a look at this code: