慢查询

SELECT  top 10

    QS.creation_time '编译计划的时间'

      , QS.last_execution_time '上次执行计划的时间'

      , QS.execution_count '执行的次数'

      , QS.total_elapsed_time / 1000 '占用的总时间（秒）'

      , QS.total_physical_reads '物理读取总次数'

      , QS.total_logical_reads/execution_count N'每次逻辑读取次数'

      , QS.total_worker_time / 1000 'CPU 时间总量（秒）'

      , QS.total_logical_writes '逻辑写入总次数'

      , QS.total_logical_reads N'逻辑读取总次数'

      , QS.total_elapsed_time / 1000 N'总花费时间（秒）'

      , 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) AS '执行语句'

FROM    sys.dm_exec_query_stats AS QS CROSS APPLY

        sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN

        ( SELECT    *

          FROM      sys.dm_exec_cached_plans cp CROSS APPLY

                    sys.dm_exec_query_plan(cp.plan_handle)

        ) DB

            ON QS.plan_handle = DB.plan_handle

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%'

           and QS.last_execution_time>'2017-09-20'

ORDER BY QS.total_logical_reads/execution_count DESC







--Blocked SQL: 
;with cte as (select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name 
, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd
,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu 


from sys.sysprocesses as a with(nolock) 
cross apply sys.dm_exec_sql_text(sql_handle) as b 
where a.blocked>0 and sql_handle<>0x0000000000000000000000000000000000000000 
and waittime>2000 ) 
select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name 
, loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,a.lastwaittype,a.cmd
,Replace(substring(b.text,1,340),'''','''') as sqlmessage,cpu 


from sys.sysprocesses as a with(nolock) 
cross apply sys.dm_exec_sql_text(sql_handle) as b 
where exists(select blocked from cte where cte.blocked=a.spid) 
and not exists (select spid from cte where cte.spid=a.spid) 
union all 
select * from cte

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16131092/viewspace-2145206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16131092/viewspace-2145206/