SQL Server CPU performance troubleshooting and optimization related SQL statements

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

-- Begin SQL related to CPU Analysis and Optimization -- use DMV to analyze 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 qorder by C. total_worker_time descgo -- returns the first 100 statements that run most frequently. Select Top 100 CP. cacheobjtype, CP. usecounts, CP. size_in_byte S, 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 qscross apply sys. dm_exec_ SQL _text (Qs. SQL _handle) as qtinner join sys. dm_exec_cached_pl Ans as CP on Qs. plan_handle = CP. plan_handlewhere CP. plan_handle = Qs. plan_handleand CP. usecounts> 4 order by [dbid], [usecounts] desc -- return the 50 statements with the maximum Io number and their execution plan 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_star T_offset, loss as stmt_end_offset, substring (SQL _text.text, (statement_start_offset/2), case when (inflow-offset)/2 <= 0 then 64000 else (inflow-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 _textcross apply sys. dm_exec_query_plan (P Lan_handle) as plan_textorder by (total_logical_reads + total_logical_writes)/execution_count desc -- calculate the percentage of signal wait to the total wait time -- the percentage of the time the command waits for CPU resources in total time. If the value exceeds 25%, the CPU usage is limited. 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 'in the total wait time -- cxpacket: SQL Server is processing a very costly statement, otherwise, the statement must return a large number of results because there is no suitable index or filtering condition to filter enough records, if> 5% indicates a problem occurs, declare @ cxpacket bigintdeclare @ sumwaits bigintselect @ cxpacket = wait_time_msfrom sys. dm_ OS _wait_statswhere wait_type = 'cxpacket 'select @ sumwaits = sum (wait_time_ms) from sys. dm_ OS _wait_statsselect convert (Numeric (5, 4), @ cxpacket/@ sumwaits) -- query the frequency of blocking all user tables in the current database on Row lock. Declare @ dbid intselect @ 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 (), 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 () from sys. dm_db_index_operational_stats (@ dbid, null, null) s, sys. indexes iWhere objectproperty (S. object_id, 'isusertable') = 1and I. object_id = S. object_idand I. index_id = S. index_idorder by row_lock_wait_count desc -- SQL related to end CPU analysis optimization