AXISHK
asked on
Active query / tasks running on SQL 2008R2
I have used the following query to find the currently Running Query on my SQL 2008 R2.
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_h andle) AS sqltext
1. For the session_id, is it equivalent to the sp_id generated from 'sp_who2'. If not, how can I tell who is running the query ?
2. for cpu_time and total_elapsed_time, is it an accumulated value. ie. will it included the value for those completed sql tasks ?
3. for unit used in cpu_time & total_elapsed_time, does it measure in ms & s respectively ?
4. Does the result come from the previous sql statement is the same as the one in the below url ?
http://gallery.technet.microsoft.com/scriptcenter/T-SQL-to-get-various-041b67d3
Tks
Tks
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_h
1. For the session_id, is it equivalent to the sp_id generated from 'sp_who2'. If not, how can I tell who is running the query ?
2. for cpu_time and total_elapsed_time, is it an accumulated value. ie. will it included the value for those completed sql tasks ?
3. for unit used in cpu_time & total_elapsed_time, does it measure in ms & s respectively ?
4. Does the result come from the previous sql statement is the same as the one in the below url ?
http://gallery.technet.microsoft.com/scriptcenter/T-SQL-to-get-various-041b67d3
Tks
Tks
ASKER
The query give more information, say what sql statement is running.
How can I make use the Activity Monitor ? The SQL always strikes out to > 50% and I can't identity which tasks in SQL causes the problem...
Task manager shows SQLserver take up this CPU usage, and I have different to identify further which processes / tasks contribute this value...
Tks
How can I make use the Activity Monitor ? The SQL always strikes out to > 50% and I can't identity which tasks in SQL causes the problem...
Task manager shows SQLserver take up this CPU usage, and I have different to identify further which processes / tasks contribute this value...
Tks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Comparing with the result of attached query above, it can't show the current cpu usage and accumulated cpu usage. It also can't tell me about the detail of the job (task), eg. what query is running ..
Any comment ?
Activity-monitor.png
Any comment ?
Activity-monitor.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tks
Why not just use the activity monitor in SSMS? It shows all you want to see in realtime.