SQL Server CPU performance troubleshooting and optimization related SQL

SQL Server CPU performance check and optimization related SQL statements, very good SQL statements, recorded here:

 --  SQL related to begin CPU analysis optimization  
  
  --  DMV is used to analyze the statements that have used the most CPU resources since SQL Server was started. For example, the following statement can list the top 50.  
  Select   
C.last_execution_time,c.exe cution_count, C. total_logical_reads, C. total_logical_writes, C. total_elapsed_time, C. last_elapsed_time,
Q.  [  Text  ] 
  From   
(  Select     Top     50  Qs.  *  
  From  SYS. dm_exec_query_stats Qs
  Order     By  Qs. total_worker_time Desc  )  As  C
  Cross  Apply SYS. dm_exec_ SQL _text (plan_handle)  As  Q
  Order     By  C. total_worker_time  Desc  
  Go  

  -- Returns the 100 most frequently run statements.  
  Select     Top     100  CP. cacheobjtype, CP. usecounts, CP. size_in_bytes, Qs. statement_start_offset, Qs. statement_end_offset, QT. dbid, QT. objectid
,  Substring  (QT.  Text  , Qs. statement_start_offset  /  2 ,
(  Case     When  Qs. statement_end_offset  =     -  1   
  Then     Len  (  Convert  (  Nvarchar (  Max  ), QT.  Text  ))  *     2   
  Else  Qs. statement_end_offset  End     -  Qs. statement_start_offset)  /  2 )  As  Statement
  From  SYS. dm_exec_query_stats Qs
  Cross  Apply SYS. dm_exec_ SQL _text (Qs. SQL _handle)  As  Qt
  Inner     Join  SYS. dm_exec_cached_plans  As  CP  On Qs. plan_handle  =  CP. plan_handle
  Where  CP. plan_handle  =  Qs. plan_handle
  And  CP. usecounts  >  4  
  Order     By     [ Dbid  ]  ,  [  Usecounts  ]     Desc  

  --  Returns 50 statements with the maximum number of I/O operations and their execution plans.  
  Select     Top     50  
(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, statement_end_offset  As  Stmt_end_offset,
  Substring  (SQL _text.  Text  , (Statement_start_offset  /  2  ),
  Case   
  When  (Statement_end_offset  - Statement_start_offset)  /  2     <=  0     Then     64000  
  Else  (Statement_end_offset  -  Statement_start_offset)  / 2     End  )  As  Exec_statement, SQL _text.  Text  , Plan_text.  *  
  From  SYS. dm_exec_query_stats
  Cross  Apply SYS. dm_exec_ SQL _text (SQL _handle)  As  SQL _text
 Cross  Apply SYS. dm_exec_query_plan (plan_handle)  As  Plan_text
  Order     By   
(Total_logical_reads  +  Total_logical_writes)  /  Execution_count  Desc  

  --  Calculate the percentage of signal wait in the total wait time 
--  The percentage of time that the command waits for CPU resources in the total time. If the CPU usage exceeds 25%, the CPU usage is insufficient.  
  Select     Convert  (Numeric (  5  ,  4  ),  Sum  (Signal_wait_time_ms)  /  Sum  (Wait_time_ms ))
 From  SYS. dm_ OS _wait_stats

  --  Calculate the percentage of 'cxpacket 'to the total wait time  
--  Cxpacket: SQL server processes a very expensive statement, or does not have a suitable index or filter condition to filter enough records, so that the statement returns a large number of results, when> 5% indicates that there is a problem  
  Declare     @ Cxpacket     Bigint  
  Declare    @ Sumwaits     Bigint  
  Select     @ Cxpacket     =  Wait_time_ms
  From  SYS. dm_ OS _wait_stats
  Where  Wait_type  =     ' Cxpacket  '  
  Select     @ Sumwaits     =     Sum  (Wait_time_ms)
  From  SYS. dm_ OS _wait_stats
  Select     Convert  (Numeric ( 5  ,  4  ),  @ Cxpacket  /  @ Sumwaits  )


  --  Queries the frequency of row lock blocking on all user tables in the current database.  
  Declare     @ Dbid     Int 
  Select     @ Dbid     =     Db_id  ()
  Select  Dbid  =  Database_id, objectname  =  Object_name  (S.  Object_id )
, Indexname  =  I. Name, I. index_id  --  , Partition_number  
  , Row_lock_count, row_lock_wait_count
,  [  Block %  ]  =  Cast  (  100.0     * Row_lock_wait_count  /  (  1     +  Row_lock_count)  As  Numeric (  15  ,  2  ))
, Row_lock_wait_in_ms
,  [  AVG row lock waits in MS  ] =  Cast  (  1.0     *  Row_lock_wait_in_ms  /  (  1     +  Row_lock_wait_count)  As  Numeric (  15 ,  2  ))
  From  SYS. dm_db_index_operational_stats (  @ Dbid  ,  Null  ,  Null  ,  Null  ) S, SYS. Indexes I
  Where     Objectproperty (S.  Object_id  ,  '  Isusertable  '  )  =     1  
  And  I.  Object_id     =  S. Object_id  
  And  I. index_id  =  S. index_id
  Order     By  Row_lock_wait_count  Desc  

  --  SQL related to end CPU analysis optimization  
  
    

RelatedArticle:

SQL Server CPU 100% common causes and Optimization

SQL Server Index usage and optimization related SQL