Sql Server 查看當前正在執行的Sql 語句

標籤：

查看Sql Server 當前正在執行的Sql 語句，可以使用 sys.sysprocesses 或 sys.dm_exec_requests，由於sys.sysprocesses是為了向後相容而保留的，所以，推薦使用DMV：sys.dm_exec_requests

 

一，使用sys.sysprocesses

Contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.

select p.spid,p.kpid,p.blocked,p.waittime,p.lastwaittype,    p.waitresource,p.dbid,p.uid,p.cpu,p.physical_io,p.memusage,    p.open_tran,p.status,p.hostname,p.program_name,    p.cmd,p.nt_domain,p.nt_username,p.loginame,    p.stmt_start,p.stmt_end,p.request_id,    s.objectid,s.text as SqlStatementfrom sys.sysprocesses p with(NOLOCK) outer apply sys.dm_exec_sql_text(p.sql_handle) swhere p.spid>50      and p.lastwaittype<>‘MISCELLANEOUS‘     and p.status<>‘sleeping‘    and p.spid<>@@spidorder by p.physical_io desc

@@SPID 表示當前的spid

一般來說，SPID<=50是system session，SPID>50的是User Session

LastWaitType 為‘MISCELLANEOUS‘ 時，it is not used for any valid wait. It is simply the default wait in a list and isn‘t used to indicate any real waiting.

關於LastWaitType ，請查看 http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

 

sql_handle 欄位表示的查詢的handle，當使用sys.dm_exec_sql_text函數擷取Sql Statement時，Sql Server會對某些包含常量的查詢語句“Auto-parameterized”，擷取的Sql statement如下，

(@P1 int,@P2 int,@P3 datetime2(7),@P4 datetime2(7))WITH CategoryIDs      AS (SELECT B.CategoryID,  .....

stmt_start和stmt_end 這兩個欄位用於標識“Auto-parameterized”增加的語句的開始和結尾。

 

二，推薦使用DMV：sys.dm_exec_requests，

select    r.session_id,r.blocking_session_id,        r.request_id,r.start_time,r.status,r.command,        st.dbid,st.objectid,st.text as SqlStatement,         SUBSTRING (st.text,                     r.statement_start_offset/2,                    (CASE WHEN r.statement_end_offset = -1                             THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2                        ELSE r.statement_end_offset END                         - r.statement_start_offset)/2                    ) as IndividualQueryStatement,        r.database_id,r.user_id,r.connection_id,        r.wait_type,r.wait_time,r.last_wait_type,r.wait_resource,r.open_transaction_count,        r.percent_complete,r.estimated_completion_time,        r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,        r.transaction_isolation_level,r.lock_timeout,r.deadlock_priority,r.row_count,        r.granted_query_memory,p.query_planfrom sys.dm_exec_requests router APPLY sys.dm_exec_sql_text(r.sql_handle) as stouter apply sys.dm_exec_query_plan(r.plan_handle) as pwhere r.last_wait_type<>‘MISCELLANEOUS‘    and r.session_id>50

三，使用DMV：sys.dm_exec_requests查看資料庫block的狀態

DMV：sys.dm_exec_requests 提供兩個非常重要的欄位是session_id，blocking_session_id

blocking_session_id

ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = The blocking resource is owned by an orphaned distributed transaction.                    

-3 = The blocking resource is owned by a deferred recovery transaction.                    

-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

SELECT  R.session_id AS BlockedSessionID ,        S.session_id AS BlockingSessionID ,        Q1.text AS BlockedSession_TSQL ,        Q2.text AS BlockingSession_TSQL ,        C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,        C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,        S.original_login_name AS BlockingSession_LoginName ,        S.program_name AS BlockingSession_ApplicationName ,        S.host_name AS BlockingSession_HostNameFROM  sys.dm_exec_requests AS RINNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_idINNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_idINNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_idCROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2

 

Appendix

如果已經知道SPID，可以使用dbcc inputbuffer(spid)來擷取spid執行的sql語句。

 

 

參考文檔

https://msdn.microsoft.com/zh-cn/library/ms179881(v=sql.110).aspx

https://msdn.microsoft.com/ZH-CN/LIBRARY/ms177648

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

https://msdn.microsoft.com/en-us/library/ms176013(SQL.90).aspx

https://msdn.microsoft.com/en-us/library/ms181509.aspx

http://blog.csdn.net/dba_huangzj/article/details/8697578

 

Sql Server 查看當前正在執行的Sql 語句