Script with high average CPU and IO overhead

--  -Long average I/O statement  Use  Tempdb  Select   Top   10  (Total_logical_reads  / Execution_count) As  Avg_logical_reads, (total_logical_writes  / Execution_count) As  Avg_logical_writes, (total_physical_reads  / Execution_count) As  Avg_phys_reads, execution_count, statement_start_offset  As  Stmt_start_offset,  Substring (St. Text , Statement_start_offset /  2   +   1  ,(  Case   When Statement_end_offset =  -  1             Then   Len ( Convert ( Nvarchar ( Max ), Text )) *   2                 Else  Statement_end_offset  End   - Statement_start_offset) / 2  ), St.  Text    --  From SYS. dm_exec_ SQL _text (SQL _handle) as query_text  From SYS. dm_exec_query_stats As  Qs  Cross Apply SYS. dm_exec_ SQL _text (Qs. SQL _handle) As  St  Order   By (Total_logical_reads + Total_logical_writes) / Execution_count Desc    /*  Return the text of SQL queries executed in batches and provide statistical information about them. From msdn, SYS. dm_exec_ SQL _text  */  Select  S2.dbid, 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 SQL _statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, worker, worker  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  S1. SQL _ HANDLE, s1.statement _ start_offset, s1.statement _ end_offset;  --  -------------------------------------  /*  -- CPU overhead-from msdn  */  Use  Adventureworks;  Go  Select   Top   5 Query_stats.query_hashAs  "Query hash ",  Sum (Query_stats.total_worker_time) /   Sum  (Query_stats.exe cution_count)  As " AVG  CPU time ",  Min (Query_stats.statement_text) As "Statement Text  "  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  Statement_text  From SYS. dm_exec_query_statsAs  Qs  Cross Apply SYS. dm_exec_ SQL _text (Qs. SQL _handle) As St) As  Query_stats  Group   By  Query_stats.query_hash  Order   By   2   Desc  ;  Go  /* Another method is to calculate the average CPU value-from msdn SYS. dm_exec_ SQL _text  */  Select   Top   5 Total_worker_time / Execution_count As   [  Avg cpu time  ]  ,  Substring (St. Text , (Qs. statement_start_offset / 2 ) +  1  ,((  Case  Qs. statement_end_offset  When   -  1   Then   Datalength (St. Text  )  Else  Qs. statement_end_offset End   - Qs. statement_start_offset) /  2 ) +   1 ) As  Statement_text  From SYS. dm_exec_query_stats As  Qs  Cross Apply SYS. dm_exec_ SQL _text (Qs. SQL _handle) As  St Order   By Total_worker_time / Execution_count Desc  ;  /*  Large overhead query --- Huang zhaoji  */  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.exe cution_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 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