Currently running queries in SQL Server

Question

Is there a program or a sql query that I can find which SQL queries are being run on an SQL Server 2012? I think there was a tool in earlier version of SQL Server where the actual query content gets displayed or the stored procedure name?

SQL profiler that is, isn't it? :) – Andrius Naruševičius May 26 '12 at 15:52 — Andrius Naruševičius, May 26 '12 at 15:52

score 34 · Answer 1 · answered Apr 01 '15 at 21:03

I use the below query

SELECT   SPID       = er.session_id
    ,STATUS         = ses.STATUS
    ,[Login]        = ses.login_name
    ,Host           = ses.host_name
    ,BlkBy          = er.blocking_session_id
    ,DBName         = DB_Name(er.database_id)
    ,CommandType    = er.command
    ,ObjectName     = OBJECT_NAME(st.objectid)
    ,CPUTime        = er.cpu_time
    ,StartTime      = er.start_time
    ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
    ,SQLStatement   = st.text
FROM    sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
    ON con.session_id = ses.session_id
WHERE   st.text IS NOT NULL

I've found this much more helpful than the other searches for queries showing running jobs. This one shows the host and login, which is great, thanks! — russds, Jan 19 '16 at 16:04

score 31 · Answer 2 · answered May 26 '12 at 15:54

31

Depending on your privileges, this query might work:

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

Ref: http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql

answered May 26 '12 at 15:54

Hari Menon

33,649
14
85
108

4

This does work, but is there any way to find out the parameter values? – jtate Jan 29 '15 at 17:31

score 9 · Answer 3 · answered Aug 21 '13 at 16:28

There's this, from SQL Server DMV's In Action book:

The output shows the spid (process identifier), the ecid (this is similar to a thread within the same spid and is useful for identifying queries running in parallel), the user running the SQL, the status (whether the SQL is running or waiting), the wait status (why it’s waiting), the hostname, the domain name, and the start time (useful for determining how long the batch has been running).

The nice part is the query and parent query. That shows, for example, a stored proc as the parent and the query within the stored proc that is running. It has been very handy for me. I hope this helps someone else.

USE master
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
er.session_Id AS [Spid]
, sp.ecid
, er.start_time
, DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]
, sp.nt_username
, er.status
, er.wait_type
, SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, sp.program_name
, sp.Hostname
, sp.nt_domain


FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY session_Id, ecid

score 7 · Accepted Answer · answered May 26 '12 at 17:00

7

here is what you need to install the SQL profiler http://msdn.microsoft.com/en-us/library/bb500441.aspx. However, i would suggest you to read through this one http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-to-sql-server-2008-profiler-2/ if you are looking to do it on your Production Environment. There is another better way to look at the queries watch this one and see if it helps http://www.youtube.com/watch?v=vvziPI5OQyE

answered May 26 '12 at 17:00

bhupendra patel

3,139
1
25
29

Unavailable video! Any backup tutorial or something simillar? – William Brochensque junior Feb 02 '23 at 18:57

score 6 · Answer 5 · answered May 26 '12 at 15:52

6

The tool is called the SQL Server Profiler, it's still part of the standard toolset.

answered May 26 '12 at 15:52

richardtallent

34,724
14
83
123

Where is the `SQL Server Profiler`? In my MS SQL Server 2012 there is no `Performance Tools` folder. I don't see any other tools. How do I install these tools. I thought everything was installed. Is Profiler still in SQL Server 2012? Where in the install are these tools? – iefpw May 26 '12 at 16:06
With a non-express version, it comes with the Management Tools option; express versions do not include Profiler – SqlACID May 26 '12 at 17:12

score 1 · Answer 6 · answered May 26 '12 at 15:53

1

You are talking about SQL Profiler.

answered May 26 '12 at 15:53

Rahul

76,197
13
71
125

Currently running queries in SQL Server

6 Answers6