当前在SQL Server中运行查询[重复]

This question already has an answer here:

这个问题在这里已有答案：

List the queries running on SQL Server 17 answers
列出在SQL Server 17上运行的查询

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查询，我可以找到在SQL Server 2012上运行的SQL查询？我认为在早期版本的SQL Server中有一个工具，其中显示实际的查询内容或存储过程名称？

6 个解决方案

#1

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

这是您需要安装SQL探查器http://msdn.microsoft.com/en-us/library/bb500441.aspx。但是，我建议你仔细阅读这篇文章http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-to-sql-server-2008-profiler-2/如果你正在寻找在您的生产环境中进行。还有另一种更好的查看查询的方法，看看它是否有帮助http://www.youtube.com/watch?v=vvziPI5OQyE

#2

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

参考：http：//blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql

#3

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

#4

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

就是这样，来自SQL Server DMV的In Action书：

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

输出显示spid（进程标识符），ecid（这类似于同一spid中的线程，对于识别并行运行的查询非常有用），运行SQL的用户，状态（SQL是否正在运行或等待）），等待状态（它为什么等待），主机名，域名和开始时间（用于确定批处理运行的时间）。

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

#5

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

该工具称为SQL Server Profiler，它仍然是标准工具集的一部分。

#6

You are talking about SQL Profiler.

您正在谈论SQL事件探查器。

#1

#2