Solved

Active query / tasks running on SQL 2008R2

Posted on 2014-01-19
6
523 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 20

Expert Comment

by:Patrick Bogers
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 20

Assisted Solution

by:Patrick Bogers
Patrick Bogers 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

792 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