SQL Server CPU performance Troubleshooting and optimization

Annotated Source:http://www.cnblogs.com/xunziji/archive/2011/07/27/2118063.html

--Begin CPU analysis optimized for related SQL--Use the DMV to analyze the statements in which SQL Server has accumulated the most CPU resources since it was started. For example, the following statement can list the top 50 names. SelectC.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time, C.last_elapsed_time, Q.[text] from (Select Top  -Qs.* fromsys.dm_exec_query_stats QSOrder  byQs.total_worker_timedesc) asC CrossApply Sys.dm_exec_sql_text (plan_handle) asQOrder  byC.total_worker_timedescGo--returns the 100 most frequently run statementsSELECT TOP  -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  whenQs.statement_end_offset= -1  Then Len(Convert(nvarchar(Max), Qt.text))* 2 ElseQs.statement_end_offsetEnd -Qs.statement_start_offset)/2) asStatement fromsys.dm_exec_query_stats QS CrossApply Sys.dm_exec_sql_text (Qs.sql_handle) asQTInner JoinSys.dm_exec_cached_plans asCp onQs.plan_handle=Cp.plan_handlewhereCp.plan_handle=Qs.plan_handle andCp.usecounts>4ORDER  by [dbid],[usecounts] DESC--returns the maximum number of IO 50 statements and their execution planSelect Top  -(Total_logical_reads/Execution_count) asavg_logical_reads, (total_logical_writes/Execution_count) asavg_logical_writes, (total_physical_reads/Execution_count) asAvg_phys_reads,execution_count, Statement_start_offset asStmt_start_offset, Statement_end_offset asStmt_end_offset,substring(Sql_text.text, (statement_start_offset/2),  Case  when(Statement_end_offset-Statement_start_offset)/2 <=0  Then 64000Else(Statement_end_offset-Statement_start_offset)/2 End) asExec_statement, Sql_text.text, Plan_text.* fromsys.dm_exec_query_stats CrossApply Sys.dm_exec_sql_text (sql_handle) asSql_text CrossApply Sys.dm_exec_query_plan (plan_handle) asPlan_textOrder  by(Total_logical_reads+Total_logical_writes)/Execution_countDesc--calculates the percentage of signal wait time for wait--the percentage of time that the instruction waits for CPU resources. If more than 25%, indicates CPU tensionSelect Convert(Numeric (5,4),sum(Signal_wait_time_ms)/sum(Wait_time_ms)) fromsys.dm_os_wait_stats--calculates the percent of ' cxpacket ' for the total wait time--Cxpacket:sql Server is dealing with a very expensive statement, or there is no proper index or filter condition to filter enough records so that the statement will return a large number of results when the >5% indicates a problemDeclare @Cxpacket bigintDeclare @Sumwaits bigintSelect @Cxpacket =Wait_time_ms fromsys.dm_os_wait_statswhereWait_type= 'Cxpacket'Select @Sumwaits = sum(Wait_time_ms) fromsys.dm_os_wait_statsSelect Convert(Numeric (5,4),@Cxpacket/@Sumwaits)--Query the frequency of blocking on row lock for all user tables on the current databaseDeclare @dbid intSelect @dbid = db_id()Selectdbid=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) asNumeric the,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) asNumeric the,2)) fromSys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s, sys.indexes iwhere ObjectProperty(S.object_id,'isusertable')= 1 andI.object_id =S.object_id andi.index_id=s.index_idOrder  byRow_lock_wait_countdesc--End CPU analysis optimized for related SQL 

SQL Server CPU performance Troubleshooting and optimization