查询指定数据库的慢语句

select top (5) max(query) as sample_query,
     sum(execution_count)as cnt,
     sum(total_worker_time) as cpu,
     sum(total_physical_reads)as reads,
     sum(total_logical_reads) as logical_reads,
     sum(total_elapsed_time) as duration
     from (
            select QS.*,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 query
        from  sys.dm_exec_query_stats as QS
         cross apply sys.dm_exec_sql_text(QS.sql_handle) as ST
         cross apply sys.dm_exec_plan_attributes(QS.plan_handle) as PA
         where PA.attribute='dbid'
          and PA.value=db_id('performance'))as d

          group by query_hash order by duration desc

 

查看语句运行时间和清空缓存

 

--清空缓存
dbcc DROPCLEANBUFFERS
--清空数据库的执行计划
dbcc freeproccache


set statistics io on
set statistics time ON