sql server 根據執行計畫查詢耗時操作

標籤：

 1 with QS as( 2 select    cp.objtype as object_type,                                                    /*類型*/ 3         db_name(st.dbid) as [database],                                                /*資料庫*/ 4         object_schema_name(st.objectid,st.dbid) as [schema],                        /*架構*/ 5         object_name(st.objectid,st.dbid) as [object],                                /*對象名*/ 6         convert(char(16),qs.creation_time,120) as plan_creation,                    /*計劃產生時間*/ 7         convert(char(16),qs.last_execution_time,120) as last_execution,                /*最後執行時間*/ 8         qs.plan_generation_num, 9         qs.execution_count,                                                            /*執行次數*/10         qs.total_elapsed_time/(1000*qs.execution_count) as avg_elapesd_seconds,        /*總花費時間ms*/11         qs.total_worker_time/(1000*qs.execution_count) as avg_cpu_cost,                /*平均cpu耗時ms*/12         qs.total_logical_reads/qs.execution_count as avg_logical_reads,                /*平均邏輯讀*/13         qs.total_logical_writes/qs.execution_count as avg_logical_writes,            /*平均邏輯寫*/14         qs.total_physical_reads/qs.execution_count as avg_physical_reads,            /*平均屋裡讀*/15         st.text,                                                                    /*執行文本*/16         qp.query_plan                                                                /*執行計畫*/17 from sys.dm_exec_query_stats qs18 join sys.dm_exec_cached_plans cp on cp.plan_handle=qs.plan_handle19 cross apply sys.dm_exec_sql_text(sql_handle) as st20 cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp21 )select top 20 * from QS22  where text like ‘%%‘23  --and object_type=‘Proc‘24  --and avg_logical_reads>20025  and execution_count>100 /*執行次數*/26  --and last_execution_time>dateadd(mi,-10,getdate())27 and last_execution >= ‘2016-05-01 00:00:00.000‘ /*最後執行時間*/28  order by avg_cpu_cost desc29  

 

sql server 根據執行計畫查詢耗時操作