List the queries running on SQL Server

Question

Is there a way to list the queries that are currently running on MS SQL Server (either through the Enterprise Manager or SQL) and/or who's connected?

I think I've got a very long running query is being execute on one of my database servers and I'd like to track it down and stop it (or the person who keeps starting it).

Answer 1

You can run the sp_who command to get a list of all the current users, sessions and processes. You can then run the KILL command on any spid that is blocking others.

Answer 2

Use Sql Server Profiler (tools menu) to monitor executing queries and use activity monitor in Management studio to see how is connected and if their connection is blocking other connections.

Answer 3

in 2005 you can right click on a database, go to reports and there's a whole list of reports on transitions and locks etc...

Answer 4

here is a query that will show any queries that are blocking. I am not entirely sure if it will just show slow queries:

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
JOIN      master..syslogins l ON p.sid = l.sid
WHERE     p.blocked = 0
AND       EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Answer 5

Actually, running EXEC sp_who2 in Query Analyzer / Management Studio gives more info than sp_who.

Beyond that you could set up SQL Profiler to watch all of the in and out traffic to the server. Profiler also let you narrow down exactly what you are watching for.

For SQL Server 2008:

START - All Programs - Microsoft SQL Server 2008 - Performance Tools - SQL Server Profiler

Keep in mind that the profiler is truly a logging and watching app. It will continue to log and watch as long as it is running. It could fill up text files or databases or hard drives, so be careful what you have it watch and for how long.

Thanks, Tom

Answer 6

There are various management views built into the product. On SQL 2000 you'd use sysprocesses. On SQL 2K5 there are more views like sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.

There are also procedures like sp_who that leverage these views. In 2K5 Management Studio you also get Activity Monitor.

And last but not least there are community contributed scripts like the Who Is Active by Adam Machanic.

Answer 7

if you're running SQL 2005 or 2008, you could use the DMV's to find this...

SELECT  *
FROM sys.dm_exec_requests  
     CROSS APPLY sys.dm_exec_sql_text(sql_handle)

• more about sys.dm_exec_requests
• more about sys.dm_exec_sql_text

Answer 8

In the Object Explorer, drill-down to: Server -> Management -> Activity Monitor. This will allow you to see all connections on to the current server.

Answer 9

This will show you the longest running SPIDs on a SQL 2000 server:

select
    p.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

If you need to see the SQL running for a given spid from the results, use something like this:

declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    master.dbo.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING( text,
      COALESCE(NULLIF(@stmt_start, 0), 1),
      CASE @stmt_end
       WHEN -1
        THEN DATALENGTH(text)
       ELSE
        (@stmt_end - @stmt_start)
       END
     )
FROM ::fn_get_sql(@sql_handle)