Solved

Active query / tasks running on SQL 2008R2

Posted on 2014-01-19
6
520 Views
Last Modified: 2014-01-24
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
Comment
Question by:AXISHK
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39792021
Hi,

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

Author Comment

by:AXISHK
ID: 39793179
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
 
LVL 19

Assisted Solution

by:Patricksr1972
Patricksr1972 earned 250 total points
ID: 39793468
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:AXISHK
ID: 39793650
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39795088
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
 

Author Closing Comment

by:AXISHK
ID: 39808282
Tks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now