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
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
All postings are provided â€œAS ISâ€ with no warranties for accuracy.
and if you wants to monitor / look for active transaction :
dbcc opentran Regards
"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: