DB中耗时的存储过程及执行详细情况代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 DB中耗时的存储过程及执行详细情况

作者:Dezai.CN / 发布于2013/3/26/ 920


	SELECT a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name',

	a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time],

	a.execution_count,

	a.total_physical_reads/a.execution_count avg_physical_reads,

	a.total_logical_writes,

	a.total_logical_writes/ a.execution_count avg_logical_reads,

	a.last_elapsed_time,

	a.total_elapsed_time / a.execution_count avg_elapsed_time,

	b.text,c.query_plan

	FROM sys.dm_exec_procedure_stats AS a

	CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

	CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

	ORDER BY [total_worker_time] DESC;

	GO


	select creation_time '编译时间',
	 last_execution_time '上次执行时间',
	 total_physical_reads '物理读取总次数',
	 total_logical_reads/execution_count '每次逻辑读次数',
	 total_logical_reads '逻辑读次数',
	 total_logical_writes '逻辑写次数',
	 execution_count '执行总次数',
	 total_worker_time/1000 '占用CPU总时间(毫秒)',
	 total_elapsed_time/1000 '总花费时间(毫秒)',
	 (execution_count/total_elapsed_time)/1000 '平均执行时间(毫秒)',
	 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) '执行的SQL语句',
	 getdate() '执行收集时间'
	 from sys.dm_exec_query_stats as qs
	cross apply sys.dm_exec_sql_text(qs.sql_handle)as st
	where 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)
	not like '%FETCH%'
	ORDER BY total_elapsed_time/execution_count desc

试试其它关键字

　存储过　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)