sqlservers 慢查询函数

本文主要是介绍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;

这篇关于sqlservers 慢查询函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！