SQLSERVER 查看表、存储过程、耗时查询、当前进程、开销较大的语句

--1、查询当前阻塞语句
SELECT TOP 500
        ds.host_name ,
        ds.program_name ,
        der.[session_id] ,
        [request_id] ,
        der.[cpu_time] ,
        [start_time] AS '开始时间' ,
        der.[status] AS '状态' ,
        [command] AS '命令' ,
        CASE WHEN statement_end_offset = -1 THEN text
             ELSE SUBSTRING(text, statement_start_offset / 2 + 1,
                            ( statement_end_offset - statement_start_offset )
                            / 2)
        END AS query_text ,
        dest.[text] AS 'sql语句' ,
        dest.objectid ,
        DB_NAME([database_id]) AS '数据库名' ,
        [blocking_session_id] AS '正在阻塞其他会话的会话ID' ,
        der.[wait_type] AS '等待资源类型' ,
        [wait_time] AS '等待时间' ,
        [wait_resource] AS '等待的资源' ,
        der.[reads] AS '物理读次数' ,
        der.[writes] AS '写次数' ,
        der.[logical_reads] AS '逻辑读次数' ,
        der.[row_count] AS '返回结果行数' ,
        percent_complete
FROM    sys.[dm_exec_requests] AS der
        INNER JOIN sys.dm_exec_sessions ds ON der.session_id = ds.session_id
        CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) qp
WHERE   --[session_id]>50 and 
        der.session_id <> @@SPID
ORDER BY der.[cpu_time] DESC;




--2、当前进程及其语句：
SELECT  PRO.loginame AS LoginName ,
        DB.name AS DatabaseName ,
        PRO.[status] AS ProcessStatus ,
        PRO.cmd AS Command ,
        PRO.last_batch AS LastBatch ,
        PRO.cpu AS Cpu ,
        PRO.physical_io AS PhysicalIo ,
        SES.row_count AS [RowCount] ,
        STM.[text] AS SQLStatement
FROM    sys.sysprocesses AS PRO
        INNER JOIN sys.databases AS DB ON PRO.dbid = DB.database_id
        INNER JOIN sys.dm_exec_sessions AS SES ON PRO.spid = SES.session_id
        CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM
WHERE   PRO.spid >= 50  -- Exclude system processes 
ORDER BY PRO.physical_io DESC ,
        PRO.cpu DESC;
 
 
 
 --3、查看数据库中表的语句消耗
SELECT TOP 10
        s2.dbid ,
        DB_NAME(s2.dbid) AS [数据库名] ,
         --s1.sql_handle ,
        ( SELECT TOP 1
                    SUBSTRING(s2.text, statement_start_offset / 2 + 1,
                              ( ( CASE WHEN statement_end_offset = -1
                                       THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
                                              * 2 )
                                       ELSE statement_end_offset
                                  END ) - statement_start_offset ) / 2 + 1)
        ) AS [语句] ,
        execution_count AS [执行次数] ,
        last_execution_time AS [上次开始执行计划的时间] ,
        total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] ,
        last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] ,
        min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] ,
        max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] ,
        total_logical_reads AS [总逻辑读] ,
        last_logical_reads AS [上次逻辑读] ,
        min_logical_reads AS [最少逻辑读] ,
        max_logical_reads AS [最大逻辑读] ,
        total_logical_writes AS [总逻辑写] ,
        last_logical_writes AS [上次逻辑写] ,
        min_logical_writes AS [最小逻辑写] ,
        max_logical_writes AS [最大逻辑写]
FROM    sys.dm_exec_query_stats AS s1
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE   s2.objectid IS NULL
ORDER BY last_worker_time DESC;



--4、查看存储过程执行的语句开销大
SELECT TOP 20
        DB_NAME(ISNULL(EPS.database_id, '')) [数据库名称] ,
        OBJECT_NAME(EPS.object_id, EPS.database_id) [存储过程名称] --AS ObjectName 
        ,
        EPS.cached_time [添加到缓存的时间]--AS CachedTime 
        ,
        EPS.last_elapsed_time '最近执行所耗费时间（微秒）'--AS LastElapsedTime
        ,
        EPS.last_worker_time '上次执行存储过程所用的CPU时间(微秒)' ,
        EPS.execution_count [上次编译以来所执行的次数]--AS ExecutionCount 
        ,
        EPS.total_worker_time / EPS.execution_count [平均每次执行所用的CPU时间总量(微秒)]--AS AvgWorkerTime 
        ,
        EPS.total_elapsed_time / EPS.execution_count [平均每次执行所用的时间(微秒)]--AS AvgElapsedTime 
        ,
        ( EPS.total_logical_reads + EPS.total_logical_writes )
        / EPS.execution_count AS AvgLogicalIO ,
        b.text [存储过程内容]
FROM    sys.dm_exec_procedure_stats AS EPS
        CROSS APPLY sys.dm_exec_sql_text(EPS.sql_handle) b
ORDER BY EPS.last_elapsed_time DESC; 
 
 
 
 
 --5、开销较大的查询：
SELECT  ss.SUM_Execution_count ,
        t.text ,
        ss.SUM_total_elapsed_time ,
        ss.SUM_total_worker_time ,
        ss.SUM_total_logical_reads ,
        ss.SUM_total_logical_writes
FROM    ( SELECT    s.plan_handle ,
                    SUM(s.execution_count) SUM_Execution_count ,
                    SUM(s.total_elapsed_time) SUM_total_elapsed_time ,
                    SUM(s.total_worker_time) SUM_total_worker_time ,
                    SUM(s.total_logical_reads) SUM_total_logical_reads ,
                    SUM(s.total_logical_writes) SUM_total_logical_writes
          FROM      sys.dm_exec_query_stats s
          GROUP BY  s.plan_handle
        ) AS ss
        CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
ORDER BY SUM_total_logical_reads DESC;