Solved

Active query / tasks running on SQL 2008R2

Posted on 2014-01-19
6
518 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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:
ScottPletcher 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

20 Experts available now in Live!

Get 1:1 Help Now