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

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

更多关于sys.dm_exec_requests的信息 更多关于sys.dm_exec_sql_text的信息

建议查询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

通过这种方式，您可以获得TotalPagesAllocated，它可以帮助您计算出占用所有服务器资源的spid。很多时候，我甚至无法打开活动监视器并使用这些系统视图来查看发生了什么。

我建议你读下面这篇文章。我从这里得到了这个参考。

下面是一个查询，它将显示任何阻塞的查询。我不完全确定它是否只会显示缓慢的查询:

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 )

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

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

试图把事情放在一起(希望有帮助):

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,
    MAX(p.loginame) AS loginame,
    (SELECT SUBSTRING(text, COALESCE(NULLIF(spid.stmt_start, 0), 1) + 1, CASE spid.stmt_end WHEN -1 THEN DATALENGTH(text) ELSE (spid.stmt_end - spid.stmt_start) END) FROM ::fn_get_sql(spid.[sql_handle])) AS [sql]
FROM
    master.dbo.sysprocesses p
    LEFT JOIN (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY spid ORDER BY ecid) AS i,
            spid,
            [sql_handle],
            CASE stmt_start WHEN 0 THEN 0 ELSE stmt_start / 2 END AS stmt_start,
            CASE stmt_end WHEN -1 THEN -1 ELSE stmt_end / 2 END AS stmt_end
        FROM sys.sysprocesses
    ) spid ON p.spid = spid.spid AND spid.i = 1
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')
GROUP BY
    p.spid,
    p.last_batch,
    p.[program_name],
    p.hostname,
    spid.stmt_start,
    spid.stmt_end,
    spid.[sql_handle]
ORDER BY
    batch_duration DESC,
    p.spid
;