1、

　　

SELECT
	spid,
	blocked,
	DB_NAME(sp.dbid) AS DBName,
	program_name,
	waitresource,
	lastwaittype,
	sp.loginame,
	sp.hostname,
	a.[Text] AS [TextData],
	SUBSTRING (
		A.text,
		sp.stmt_start / 2,
	( CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH( A.text ) ELSE sp.stmt_end END - sp.stmt_start ) 
	) AS [current_cmd],* 
FROM
	MASTER.dbo.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text ( sp.sql_handle ) AS A
	

　　

2、

    

SELECT SPID=p.spid,
       DBName = convert(CHAR(20),d.name),
       ProgramName = program_name,
       LoginName = convert(CHAR(20),l.name),
       HostName = convert(CHAR(20),hostname),
       Status = p.status,
       BlockedBy = p.blocked,
       LoginTime = login_time,
       QUERY = CAST(TEXT AS VARCHAR(MAX))
FROM   MASTER.dbo.sysprocesses p
       INNER JOIN MASTER.dbo.sysdatabases d
         ON p.dbid = d.dbid
       INNER JOIN MASTER.dbo.syslogins l
         ON p.sid = l.sid
       CROSS APPLY sys.dm_exec_sql_text(sql_handle)

　　

　3、

       

SELECT TOP
	100 ( total_elapsed_time / execution_count ) / 1000000 N'平均时间s',
	total_elapsed_time / 1000000 N'总花费时间s',
	total_worker_time / 1000000 N'所用的CPU总时间s',
	total_physical_reads N'物理读取总次数',
	total_logical_reads / execution_count N'每次逻辑读次数',
	total_logical_reads N'逻辑读取总次数',
	total_logical_writes N'逻辑写入总次数',
	execution_count N'执行次数',
	qs.statement_start_offset,
	st.text,
	statement_end_offset,
	SUBSTRING (
		st.text,
		( qs.statement_start_offset/ 2 ) + 1,
	( ( CASE statement_end_offset WHEN - 1 THEN DATALENGTH( st.text ) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 
	) N'执行语句',
	creation_time N'语句编译时间',
	last_execution_time N'上次执行时间' 
FROM
	sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) st 
ORDER BY
	total_elapsed_time / execution_count DESC;