Solved

Find current query running in sql server 2005

Posted on 2011-03-08
4
1,306 Views
Last Modified: 2012-05-11
HI,
I am running SQL profiler to troubleshoot a long running query.  I am trying to figure out a query that is taking a very long time.  There are a few applications running on this server.  I know I can use profiler to get the “SQL:BatchStarting” and the “SQL:BatchCompleted” to capture the TextData and find out the SQL query that is running and how long it took.  My question is how can I see the current query that is running in SQL Server without having to wait until it is completed.   Some of the queries are taking a very long time (may even abort at the end) and makes it difficult to troubleshoot and find out which one it was.   Is there a way to run a SQL Command to see the current running queries on the server?
Hope I was clear enough.
Thanks,
Miguel

FYI:  I can do this in Oracle running the following command.  Looking for the same in SQL Server.

 SELECT
      substr(a.username,1,10) UserName,a.sid, substr(a.osuser,1,10) osuser, p.spid OS_PID, a.AUDSID, substr(a.process,1,10) Session_ID,a.serial#,a.Last_Call_ET, a.command, substr(a.lockwait,1,5) lockwait ,substr(a.machine,1,17) machine ,substr(a.program,1,10) program,b.piece,b.sql_text
FROM
v$session a, v$sqltext_with_newlines b,v$process p
WHERE
a.status = 'ACTIVE' AND a.username <> 'SYS'
AND      a.sql_hash_value= b.hash_value
AND     a.paddr = p.addr
ORDER BY osuser,sid,piece;
0
Comment
Question by:agcsupport
4 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 35072248
Try this query.

http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/
SELECT sqltext.TEXT, 
       req.session_id, 
       req.status, 
       req.command, 
       req.cpu_time/(60*1000) cpu_time_in_minuetes, 
       req.total_elapsed_time/(60*1000) total_elapsed_time_in_minutes 
  FROM sys.dm_exec_requests req 
       CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Open in new window

0
 
LVL 2

Assisted Solution

by:EL_Barbado
EL_Barbado earned 250 total points
ID: 35072315
Hi,
You can use this SQL statement (or a variation) to find the information that you need.

SELECT Object_name(objectid), SQLText.text, *
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text ( sp.sql_handle ) SQLText

This will show you the name of the stored procedure (if its a procedure) and the exact batch that is being executed along with other information about the process. You can include a WHERE clause to filter the data to you liking.

I hope this helps!
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072332
If you can create procedures on you server I's suggest you to use Adam Machanic's  Who Is Active stored procedure. It's like a sp_who2 on steroids.
Give it a try if you want to:
http://sqlblog.com/files/folders/release/default.aspx
0
 

Author Closing Comment

by:agcsupport
ID: 35072358
Both very usefull.  Thanks a lot.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 58
T-SQL to Update Table Dynamically 2 55
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 69
Caste datetime 2 62
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

790 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