See what queries are currently running

Category : Tips User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
Sometimes it is useful to see what is currently running on a SQL server. On SQL Server 2005 and later this is fairly easy to achieve using the Dynamic Management View dm_exec_requests :
SELECT * FROM sys.dm_exec_requests
This has a row for each request that is currently executing. Many of these will be internal sessions used by SQL Server but it will also include SQL commands currently executing. To filter out the internal sessions ignore any with a session_id of 50 or less.

The query can be extended to display the actual SQL executing, using the dm_exec_sql_text DMV, which is called using the APPLY operator :
SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
            Status,command,wait_type,wait_time,wait_resource,
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time
I’ve reduced the number of columns that are displayed to make things a little clearer, but added in the SQL command executed, where there is one. I’ve also eliminated any system sessions and the current session (SPID) being used to run this query.

That’s about it really !

One thing you might occasionally get is the following error message :
Msg 321, Level 15, State 1, Line 8
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
As I mentioned DMVs were introduced in SQL Server 2005, so the queries won’t work with earlier versions. However even if you are running SQL Server 2005 it’s possible that the compatibility mode of the database you are running the query against is SQL 2000 or earlier. In this case the OUTER APPLY syntax won’t work. Fortunately this is easy to resolve by running the query against the master database or another database that has 90 or 100 compatibility – the query is not database specific so it won’t affect the results.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/See what queries are currently running.aspx

Keywords

TSQL,query,queries,active,running


Comments
Post by aravindh on Fri 02 May 2014 17:45. Report Inappropriate Post

Changing compatibility level at production environment is disastrous. Applications connected to the databases may not work properly.
Post by Chris on Tue 27 Oct 2015 09:25. Report Inappropriate Post

Is there anyway to see what code is been run?
Post by Lok on Wed 26 Oct 2016 12:36. Report Inappropriate Post

Really awesome! Thanks.
Post by dheeraj on Wed 26 Jun 2024 14:24. Report Inappropriate Post

set nocount on
if (select object_id('tempdb.dbo.RestoreUserSyntax')) > 0
drop table tempdb.dbo.RestoreUserSyntax
if (select object_id('tempdb.dbo.RestoreUserSyntax1')) > 0
drop table tempdb.dbo.RestoreUserSyntax1
go

Declare @RemoveExistingUsers int
set @RemoveExistingUsers = 0 -- Set to 0 if you do not want to remove users after a database refresh (test to Prod it should be 1 and from prod to test it can be 0)

declare @dbname nvarchar(512)
select @dbname = DB_NAME()
declare @Recovery varchar(100)

if( @dbname <> 'master' and @dbname <> 'tempdb' and @dbname <> 'msdb' and @dbname <> 'model' )
begin

declare @version int

set @version = (select cast(substring(CAST(serverproperty('ProductVersion') as varchar(50)), 1,patindex('%.%',CAST(serverproperty('ProductVersion') as varchar(50)))-1 ) as int))
Set @Recovery = (select cast(databasepropertyex(@dbname, 'Recovery') as varchar(100)))
--select @version

create table tempdb.dbo.RestoreUserSyntax
(
CatagoryID int identity(1,1),
syntax varchar(4000)
)
create table tempdb.dbo.RestoreUserSyntax1
(
CatagoryID int identity(1,1),
syntax varchar(4000)
)
If @version = 8
Begin
-- Extract Current Database
insert into tempdb.dbo.RestoreUserSyntax1 select 'use ['+DB_NAME()+']'
insert into tempdb.dbo.RestoreUserSyntax1 select 'go'

If @RemoveExistingUsers = 1 -- If 1 remove existing users first
Begin
insert into tempdb.dbo.RestoreUserSyntax1 select 'set nocount on Declare @exec1 varchar(2000) DECLARE DPusers CURSOR FOR '
insert into tempdb.dbo.RestoreUserSyntax1 select 'select ''if not exists (select name name from sysobjects where uid = USER_ID(''''''+name+'''''')) exec sp_revokedbaccess [''+name+'']'' from sysusers where not islogin = 0 and not (issqlrole = 1 or isapprole =1) and uid > 3 FOR READ ONLY '
insert into tempdb.dbo.RestoreUserSyntax1 select 'OPEN DPusers FETCH NEXT FROM DPusers INTO @exec1 WHILE @@FETCH_STATUS = 0 BEGIN

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
 Not displayed Optional, but displayed if entered