列出在SQL Server上运行的查询

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?

是否有一种方法列出当前在MS SQL Server上运行的查询(通过企业管理器或SQL)和/或连接的人?

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).

我想我有一个很长的查询正在我的一个数据库服务器上执行，我想要跟踪它并停止它(或者继续启动它的人)。

16 个解决方案

#1

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

这将向您展示SQL 2000或SQL 2005服务器上运行时间最长的SPIDs:

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:

如果您需要查看结果中为给定spid运行的SQL，请使用以下内容:

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)

#2

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

如果您正在运行SQL Server 2005或2008，您可以使用DMV找到这个……

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

• More about sys.dm_exec_requests
• 更多关于sys.dm_exec_requests
• More about sys.dm_exec_sql_text
• 更多关于sys.dm_exec_sql_text

#3

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.

您可以运行sp_who命令获取所有当前用户、会话和进程的列表。然后，您可以对任何阻塞其他的spid运行KILL命令。

#4

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.

产品中内置了各种管理视图。在SQL 2000上，您将使用sysprocess。在SQL 2K5上有更多像sys这样的视图。dm_exec_connections sys。dm_exec_sessions sys.dm_exec_requests。

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

还有一些过程，如sp_who利用这些视图。在2K5 Management Studio中，您还可以获得活动监视器。

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

最后但并非最不重要的是有一些社区贡献的剧本，比如亚当·马赫尼克的《世界卫生组织》。

#5

I would suggest querying the sys views. something similar to

我建议查询sys视图。类似于

SELECT * 
FROM 
   sys.dm_exec_sessions s
   LEFT  JOIN sys.dm_exec_connections c
        ON  s.session_id = c.session_id
   LEFT JOIN sys.dm_db_task_space_usage tsu
        ON  tsu.session_id = s.session_id
   LEFT JOIN sys.dm_os_tasks t
        ON  t.session_id = tsu.session_id
        AND t.request_id = tsu.request_id
   LEFT JOIN sys.dm_exec_requests r
        ON  r.session_id = tsu.session_id
        AND r.request_id = tsu.request_id
   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL

This way you can get a TotalPagesAllocated which can help you figure out the spid that is taking all the server resources. There has lots of times when I can't even bring up activity monitor and use these sys views to see what's going on.

通过这种方式，您可以获得一个totalpagesallocation，它可以帮助您找出占用所有服务器资源的spid。很多时候，我甚至不能打开activity monitor并使用这些sys视图来查看发生了什么。

I would recommend you reading the following article. I got this reference from here.

我建议您阅读下面的文章。我从这里得到了这个参考。

#6

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

实际上，在Query Analyzer / Management Studio中运行EXEC sp_who2可以提供比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.

除此之外，还可以设置SQL分析器来监视到服务器的所有进出流量。Profiler还可以让您精确地缩小您正在监视的内容。

For SQL Server 2008:

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.

请记住，profiler实际上是一个日志记录和观察应用程序。只要它运行，它将继续记录和观察。它可能会填满文本文件、数据库或硬盘，所以要注意它会监视什么内容以及监视多长时间。

#7

SELECT
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
    p.program_name, text 
FROM
    sys.dm_exec_requests AS r,
    master.dbo.sysprocesses AS p 
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
    p.status NOT IN ('sleeping', 'background') 
AND r.session_id = p.spid

#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.

在对象资源管理器中，深入到:Server ->管理->活动监视器。这将允许您查看到当前服务器上的所有连接。

#9

The right script would be like this:

正确的脚本应该是这样的:

select 
p.spid, p.status,p.hostname,p.loginame,p.cpu,r.start_time, t.text
    from sys.dm_exec_requests as r, sys.sysprocesses p 
    cross apply sys.dm_exec_sql_text(p.sql_handle) t
    where p.status not in ('sleeping', 'background')
    and r.session_id=p.spid

#10

As a note, the SQL Server Activity Monitor for SQL Server 2008 can be found by right clicking your current server and going to "Activity Monitor" in the context menu. I found this was easiest way to kill processes if you are using the SQL Server Management Studio.

注意，可以通过右键单击当前服务器并在上下文菜单中“Activity Monitor”找到SQL Server 2008的SQL Server活动监视器。我发现，如果您正在使用SQL Server Management Studio，那么这是杀死进程的最简单方法。

#11

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...

在2005年，你可以右键单击一个数据库，转到报告，有关于转换和锁等的完整报告列表……

#12

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 )

#13

You can use below query to find running last request:

您可以使用下面的查询来查找最后的请求:

SELECT
    der.session_id
    ,est.TEXT AS QueryText
    ,der.status
    ,der.blocking_session_id
    ,der.cpu_time
    ,der.total_elapsed_time
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est

Using below script you can also find number of connection per database:

使用下面的脚本，您还可以找到每个数据库的连接数:

SELECT 
    DB_NAME(DBID) AS DataBaseName
    ,COUNT(DBID) AS NumberOfConnections
    ,LogiName 
FROM sys.sysprocesses
WHERE DBID > 0
GROUP BY DBID, LogiName

For more details please visit: http://www.dbrnd.com/2015/06/script-to-find-running-process-session-logged-user-in-sql-server/

详情请访问:http://www.dbrnd.com/2015/06/script-to-find-run -process-session-logged-user-in-sql-server/

#14

SELECT 
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, t.text
FROM
    sys.dm_exec_requests as r,
    master.dbo.sysprocesses as p
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) t
WHERE
    p.status NOT IN ('sleeping', 'background')
AND r.session_id = p.spid

And

和

KILL @spid

#15

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.

使用Sql Server Profiler(工具菜单)监视执行查询，并使用Management studio中的activity monitor查看如何连接，以及它们的连接是否阻塞了其他连接。

#16

You should try very usefull procedure sp_whoIsActive which can be found here: http://sqlblog.com/files/default.aspx and it is free.

您应该尝试非常有用的sp_whoIsActive过程，可以在这里找到:http://sqlblog.com/files/default.aspx，它是免费的。