查询IO的几个存储过程

爱被打了一巴掌 2022-06-17 13:51 173阅读 0赞

-- =============================================
    -- 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