SQL Server to troubleshoot high CPU usage

Phenomenon

He said it was only a few days ago, and at a certain time of the day, there will be high CPU usage

Memory consumption is not high, only 30 g

CPU Consumption 100%

Troubleshooting Directions

General troubleshooting is to use the following script, generally use three views sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

1 Use Master2 GO3--Removes the comment 4 SELECT * FROM SYS if you want to specify a database. [sysprocesses] WHERE [spid]>50--and db_name ([dbid]) = ' gposdb ' 5 SELECT COUNT (*) from [SYS]. [Dm_exec_sessions] WHERE [session_id]>50

Take a look at the current database user connection how many

Then use the following statement to see whether the indicators are normal, whether there is blocking, this statement selected the top 10 CPU-intensive sessions

1 SELECT TOP 2 [session_id], 3 [request_id], 4 [start_time] as ' Start time ', 5 [status] as ' status ', 6 [command] as ' command ', 7 dest. [Text] As ' SQL statement ',  8 db_name ([database_id]) as ' database name ', 9 [blocking_session_id] as ' blocking session ID for other sessions ', [Wait_type] as ' waiting for resource type ' , one [wait_time] as ' waiting time ', [Wait_resource] as ' waiting resource ', [reads] as ' physical read count ', [writes] as ' write times ', [logical_reads] as ' Number of logical reads ', [Row_count] as ' returns the number of result rows ' from sys. [Dm_exec_requests] As Der Cross APPLY sys. [Dm_exec_sql_text] (Der.[sql_handle]) As Dest WHERE [session_id]>50 and Db_name (der.[database_id]) = ' Gposdb '  ORDER by [Cpu_time] DESC

If you want to see a specific SQL statement can execute the following SQL statement, remember to choose to display the results in the text format in SSMs

1--In SSMS Select Display results in text Format 2 Select TOP 3 dest. [Text] As ' SQL statement ' 4 from sys. [Dm_exec_requests] As Der 5 cross APPLY 6 sys. [Dm_exec_sql_text] (Der.[sql_handle]) As Dest 7 WHERE [session_id]>50  8 ORDER by [Cpu_time] DESC

Simulate some of the CPU time-consuming actions

There are also the number of CPU and user scheduler and the maximum number of worker threads, to check whether the worker is exhausted or to troubleshoot CPU usage

1--View CPU count and number of User Scheduler 2 Select Cpu_count,scheduler_count from Sys.dm_os_sys_info3--view maximum number of worker threads 4 SELECT Max_workers_ Count from Sys.dm_os_sys_info

View all schedulers on the machine including user and system
Use the following statement to see if the worker is exhausted, and check the blocking when the maximum number of threads is reached

Control the table below.
Maximum number of worker threads automatically configured for various CPU and SQL Server version combinations
Number of CPUs 32-bit computer 64-bit computer
<=4 256 512
8 288 576
16 352 704
32 480 960

1 SELECT2 scheduler_address,3 scheduler_id,4 cpu_id,5 status,6 current_tasks_count,7 current_workers_count,active_ Workers_count8 from Sys.dm_os_schedulers

If SQL Server has a resource to wait for, executing the following statement shows how many workers in the session are waiting

combined with [SYS]. [Dm_os_wait_stats] View, if there are no waiting resources in the current SQL Server, then the following SQL statement will not show any results

1 SELECT TOP 2  [session_id], 3  [request_id], 4  [start_time] as ' Start time ', 5  [status] as ' status ', 6  [comma nd] as ' command ', 7  dest.[ Text] as ' SQL statement ',  8  db_name ([database_id]) as ' database name ', 9  [blocking_session_id] as ' blocking session ID for other sessions ',  Der.[wait_type] as ' wait for resource type ', one  [wait_time] as ' Wait time ',  [Wait_resource] as ' waiting resource ',  [dows].[ Waiting_tasks_count] as ' number of tasks currently in waiting ', [  reads] as ' physical reads ',  [writes] as ' write times ', [  logical_reads] As ' logical reads ',  [row_count] as ' returns the number of results rows ' from  sys.[ Dm_exec_requests] as der  INNER JOIN [sys].[ Dm_os_wait_stats] As dows on  der.[wait_type]=[dows].[ Wait_type]21 Cross  APPLY  sys.[ Dm_exec_sql_text] (Der.[sql_handle]) as Dest  WHERE [session_id]>50  ORDER by  [Cpu_time] DESC

For example, I am currently executing the query Salesorderdetail_test table 100 times, because the table data is very much, so SSMs needs to take the results of SQL Server execution slowly take away,

Cause the async_network_io to wait

1 Use [adventureworks]2 GO3 SELECT * FROM dbo. [Salesorderdetail_test]4 GO 100

Source of the problem

After troubleshooting and observing these days, determine that some table-missing indexes are causing the indexes that are now added to these tables, problem solving

1 SELECT * FROM T_accesscontrol        --Permissions Control Table permission control 2 Select * FROM t_groupaccess            -user Group Permissions table user group permissions 3 SELECT * FROM T_groupacces Stype        --User group Permissions Class table user group permissions Class 4 SELECT * from T_objectaccess        --Object Permission Table object permissions 5 Select * FROM T_objectaccesstype    -- Object permission Type Table object permission type 6 select * FROM T_objecttype            --Object Type Table object type

Querying high CPU-intensive statements

1 SELECT TOP 2    total_worker_time/execution_count as Avg_cpu_cost, plan_handle, 3    execution_count, 4    ( SELECT SUBSTRING (text, STATEMENT_START_OFFSET/2 + 1, 5       (case is statement_end_offset =-1 6 then          LEN (CONVERT (NV Archar (max), text)) * 2 7          ELSE statement_end_offset 8       end-statement_start_offset)/2) 9 from    sys.dm_exec_ Sql_text (sql_handle)) as query_text10 from Sys.dm_exec_query_stats11 ORDER by [Avg_cpu_cost] DESC

Query Missing Index

1 SELECT 2     DatabaseName = db_name (database_id) 3     , [number Indexes Missing] = count (*) 4 from Sys.dm_db_missing_i NDEX_DETAILS5 GROUP by Db_name (database_id) 6 ORDER by 2 DESC;

1 SELECT  TOP  2 [Total cost         ]  = ROUND (Avg_total_user_cost * avg_user_impact * (User_seeks + User_scans) , 0)  3         , avg_user_impact 4         , TableName = statement 5         , [equalityusage] = equality_columns  6         , [ Inequalityusage] = inequality_columns 7         , [Include cloumns] = Included_columns 8 from        Sys.dm_db_missing_index _groups g  9 INNER JOIN    sys.dm_db_missing_index_group_stats s ten on        s.group_handle = G.index_group_ Handle INNER JOIN    sys.dm_db_missing_index_details d in        d.index_handle = G.index_handle13 ORDER by [Total C OST] DESC;

After locating the problem, create a new nonclustered index

1 CREATE nonclustered INDEX ix_t_accesscontrol_f4 on dbo.t_accesscontrol2 (3     FObjectType4) include ([Fuserid], [ Faccesstype], [Faccessmask]) with (Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = ON, Allow_page_lo CKS = ON) on [primary]5 GO6 7 Drop index IX_T_ACCESSCONTROL_F4 on T_accesscontrol

CPU usage back to normal

Trace template and trace file download, use SQL2008R2 version: files.cnblogs.com/lyhabc/trace Template and Trace.rar

Summarize

From a number of historical experience, if the CPU load continues to be very high, but the memory and Io are OK, in this case, the first thought must be the index problem, nine out of ten can not be wrong.

Note the customer machine load situation diagram at the beginning of the article

SQL Server to troubleshoot high CPU usage