• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
  • 3
  • 2
2 Solutions
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi,

Why not just use the activity monitor in SSMS? It shows all you want to see in realtime.
0
 
AXISHKAuthor Commented:
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
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

There are two options to get there and it will show you what you need to see.
Click here to see a picture where it is in SMSS.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
AXISHKAuthor Commented:
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
0
 
Scott PletcherSenior DBACommented:
1) == Yes

2) & 3) == See the Books Online entry for "sys.dm_exec_requests" -- it will explain every column in the view.

4) ? == I guess run them both and compare the results -- you should be able to do that as well as I can.
0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now