简介
大家都知道使用sp_who2这个命令来查看当前正在的执行的SQL语句的spids.但是显示
的信息有限.比如,它只显示执行SELECT,DELETE,UPDATE等.看不到实际的sql命令.
知道正在执行哪条sql语句对于我们debug程序,找出哪些语句花费很长时间,或是哪些语句产生
堵塞来说非常重要.而且这个常用来查看存储过程执行到哪一步.执行到哪个语句.
所以我们需要一个比sp_who2更好的命令来做这些工作.
这个命令使用SQL的动态管理视图 Dynamic Management Views (DMVs).所以只用执行在SQL2005或是
更高的版本上.
返回字段简介:
| 列名 | 类型 | 描述 |
| spid | smallint | SQL Server process ID. |
| ecid | smallint | Econtext ID |
| dbid | smallint | 数据库ID |
| nt_username | nchar(128) | 用户名 |
| status | nchar(30) | 状态 |
| wait_type | bigint | 当前等待的 milliseconds. |
| Individual Query | varchar | 当前执行的SQL语句 |
| Parent Query | varchar | 上层的SQL语句 |
| program_name | nchar(128) | 程序名称 |
| Hostname | nchar(128) | 主机名 |
| nt_domain | nchar(128) | 域名 |
| Start_time | datetime | 开始时间 |
CREATE PROC [dbo].[dba_WhatSQLIsExecuting] AS /*-------------------------------------------------------------------- Purpose: Shows what individual SQL statements are currently executing. ---------------------------------------------------------------------- Parameters: None. Revision History: 24/07/2008 Ian_Stirk@yahoo.com Initial version Example Usage: 1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting ---------------------------------------------------------------------*/ BEGIN -- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- What SQL Statements Are Currently Running? SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status , [Wait] = wait_type , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (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) ,[Parent Query] = qt.text , Program = program_name , Hostname , nt_domain , start_time 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 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. ORDER BY 1, 2 END
from http://www.diybl.com/course/7_databases/sql/sqlServer/20090520/167430.html