Link to home
Start Free TrialLog in
Avatar of AXISHK
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_handle) 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
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Hi,

Why not just use the activity monitor in SSMS? It shows all you want to see in realtime.
Avatar of AXISHK
AXISHK

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
SOLUTION
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AXISHK

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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AXISHK

ASKER

Tks