SQL Server View the currently executing SQL statement

Look at the SQL statement that SQL Server is currently executing, and you can use sys.sysprocesses or sys.dm_exec_requests. Since sys.sysprocesses is reserved for backwards compatibility, it is recommended to use DMV:sys.dm_exec_requests

One, using sys.sysprocesses

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

SelectP.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_st art,p.stmt_end,p.request_id, S.objectid,s.text  asSQLStatement fromSys.sysprocesses P with(NOLOCK)outerapply Sys.dm_exec_sql_text (p.sql_handle) swhereP.spid> -       andP.lastwaittype<>'Miscellaneous'      andP.status<>'Sleeping'     andP.spid<>@ @spidOrder  byP.physical_iodesc

@ @SPID represents the current SPID

In general, SPID<=50 is the user session of the system session,spid>50

When Lastwaittype is 'miscellaneous',It's not used for any valid wait. It is simply the default wait in a list and isn ' t used to indicate any real waiting.

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

The Sql_handle field represents the handle of the query, and when you use the Sys.dm_exec_sql_text function to get SQL statement, SQL Server "auto-parameterized" for some query statements that contain constants, Get the SQL statement as follows,

(@P1int,@P2int,@P3 datetime2 (7),@P4 datetime2 ( 7  ) with Categoryids as        (SELECT  B.categoryid,  .....

The Stmt_start and Stmt_end fields are used to identify the beginning and end of the added statement "auto-parameterized".

Second, we recommend the use of DMV:sys.dm_exec_requests,

Selectr.session_id,r.blocking_session_id, R.request_id,r.start_time,r.status,r.command, St.dbid,st.objecti D,st.text  asSQLStatement,SUBSTRING(St.text, R.statement_start_offset/2,                    ( Case  whenR.statement_end_offset= -1                              Then LEN(CONVERT(NVARCHAR(MAX), St.text))* 2                        ELSER.statement_end_offsetEND                         -R.statement_start_offset)/2                    )  asindividualquerystatement, 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.P        Ercent_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_PL an fromsys.dm_exec_requests RouterAPPLY sys.dm_exec_sql_text (R.sql_handle) asStouterApply Sys.dm_exec_query_plan (R.plan_handle) asPwhereR.last_wait_type<>'Miscellaneous'     andr.session_id> -

Third, use DMV:sys.dm_exec_requests to view the status of the database block

DMV:sys.dm_exec_requests provides two very important fields that are session_id,blocking_session_id

blocking_session_id

ID of the session is blocking the request. If This column is NULL, the request isn't blocked, or the session information of the blocking session is not availabl E (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 being determined at this time because of internal in state trans Itions.

SELECTr.session_id asBlockedsessionid, s.session_id asBlockingsessionid, Q1.text  asBlockedsession_tsql, Q2.text  asBlockingsession_tsql, C1.most_recent_sql_handle asBlockedsession_sqlhandle, C2.most_recent_sql_handle asBlockingsession_sqlhandle, S.original_login_name asBlockingsession_loginname, S.program_name asblockingsession_applicationname, S.host_name  asBlockingsession_hostname fromSys.dm_exec_requests asRINNER JOINSys.dm_exec_sessions asS onr.blocking_session_id=s.session_idINNER JOINSys.dm_exec_connections asC1 onr.session_id=c1.most_recent_session_idINNER JOINSys.dm_exec_connections asC2 ons.session_id=c2.most_recent_session_id CrossAPPLY sys.dm_exec_sql_text (C1.most_recent_sql_handle) asQ1 CrossAPPLY sys.dm_exec_sql_text (C2.most_recent_sql_handle) asQ2

Appendix

If you already know the SPID, you can use DBCC INPUTBUFFER (SPID) to get the SQL statement that the SPID executes.

Reference documents

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 View the currently executing SQL statement