查询IO的几个存储过程代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】查询IO的几个存储过程
作者:Dezai.CN / 发布于2013/3/26/ 838

	-- =============================================
	-- Author: ZHAOWENZHONG
	-- Create date: 2009-05-17
	-- Description: 查询某个会话IO
	-- =============================================
	CREATE PROCEDURE [dbo].[Pr_Who_IO]

	( @SessionID int = NULL )

	AS
	BEGIN
	SET NOCOUNT ON
	SELECT
	 SPID = er.session_id
	 ,Status = ses.status
	 ,[Login] = ses.login_name
	 ,Host = ses.host_name
	 ,BlkBy = er.blocking_session_id
	 ,DBName = DB_Name(er.database_id)
	 ,CommandType = er.command
	 ,SQLStatement = st.text
	 ,ObjectName = OBJECT_NAME(st.objectid)
	 ,ElapsedMS = er.total_elapsed_time
	 ,CPUTime = er.cpu_time
	 ,IOReads = er.logical_reads + er.reads
	 ,IOWrites = er.writes
	 ,LastWaitType = er.last_wait_type
	 ,StartTime = er.start_time
	 ,Protocol = con.net_transport
	 ,ConnectionWrites = con.num_writes
	 ,ConnectionReads = con.num_reads
	 ,ClientAddress = con.client_net_address
	 ,Authentication = con.auth_scheme

	FROM sys.dm_exec_requests er
	OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
	LEFT JOIN sys.dm_exec_sessions ses
	ON ses.session_id = er.session_id
	LEFT JOIN sys.dm_exec_connections con
	ON con.session_id = ses.session_id
	WHERE er.session_id > 50
	 AND @SessionID IS NULL OR er.session_id = @SessionID
	ORDER BY
	 er.blocking_session_id DESC
	 ,er.session_id
	SET NOCOUNT OFF
	END

	GO

	
	-- =============================================
	-- Author: ZHAOWENZHONG
	-- Create date: 2009-05-17
	-- Description: 查询DB的IO开销
	-- =============================================
	CREATE PROCEDURE Pr_DB_IO
	AS
	BEGIN
	SET NOCOUNT ON
	SELECT creation_time N'语句编译时间'
	
	 ,last_execution_time N'上次执行时间'
	
	 ,total_physical_reads N'物理读取总次数'
	
	 ,total_logical_reads/execution_count N'每次逻辑读次数'
	
	 ,total_logical_reads N'逻辑读取总次数'
	
	 ,total_logical_writes N'逻辑写入总次数'
	
	 , execution_count N'执行次数'
	
	 , total_worker_time/1000 N'所用的CPU总时间ms'
	
	 , total_elapsed_time/1000 N'总花费时间ms'
	
	 , (total_elapsed_time / execution_count)/1000 N'平均时间ms'
	
	 ,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'执行语句'
	
	FROM sys.dm_exec_query_stats AS qs
	
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
	
	where 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) not like '%FETCH%'
	
	ORDER BY total_elapsed_time / execution_count DESC;
	SET NOCOUNT OFF
	END
	go

	-- =============================================
	-- Author: ZHAOWENZHONG
	-- Create date: 2009-05-17
	-- Description: 查询最大的IO开销SQL
	-- =============================================
	--默认取 50个io读取开销最大的语句
	CREATE PROCEDURE Pr_Max_IO
	(@Top int =50)
	as
	BEGIN
	SET NOCOUNT ON
	SELECT TOP (@Top) SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
	
	 ((CASE qs.statement_end_offset

	 WHEN -1 THEN DATALENGTH(qt.text)

	 ELSE qs.statement_end_offset

	 END - qs.statement_start_offset)/2)+1),

	 qs.execution_count,

	 qs.total_logical_reads, qs.last_logical_reads,

	 qs.min_logical_reads, qs.max_logical_reads,

	 qs.total_elapsed_time, qs.last_elapsed_time,

	 qs.min_elapsed_time, qs.max_elapsed_time,

	 qs.last_execution_time,

	 qp.query_plan
	
	FROM sys.dm_exec_query_stats qs
	
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
	
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
	
	WHERE qt.encrypted=0
	
	ORDER BY qs.total_logical_reads DESC
	SET NOCOUNT OFF
	END

	
	GO

	--得到按照执行时间排序的前10 的存储过程的执行信息：

	SELECT TOP 10 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
试试其它关键字
　查询IO　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)