sqlservers 慢查询函数

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;

4、正在执行的sql

   

SELECT
der.[session_id],der.[blocking_session_id],
sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,
der.[start_time] AS '开始时间',
der.[status] AS '状态',
dest.[text] AS 'sql语句',
DB_NAME(der.[database_id]) AS '数据库名',
der.[wait_type] AS '等待资源类型',
der.[wait_time] AS '等待时间',
der.[wait_resource] AS '等待的资源',
der.[logical_reads] AS '逻辑读次数'
FROM sys.[dm_exec_requests] AS der
INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid
CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
--WHERE [session_id]>50 AND session_id<>@@SPID
ORDER BY der.[session_id]
GO

　　

    5、锁表的sql

SELECT spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran 
 ,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text 
 FROM master.dbo.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) s 
 WHERE blocked > 0 OR spid IN(SELECT blocked FROM master.dbo.sysprocesses WHERE blocked > 0) 
go　　

6、

SELECT TOP 100
 (total_logical_reads + total_logical_writes) AS total_logical_io,
 (total_logical_reads / execution_count) AS avg_logical_reads,
(total_logical_writes / execution_count) AS avg_logical_writes,
(total_physical_reads / execution_count) AS avg_phys_reads,
substring (st.text,
(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1
 THEN datalength (st.text)
ELSE qs.statement_end_offset END
 - qs.statement_start_offset)/ 2)+ 1)
 AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS st
ORDER BY total_logical_io desc