SQL Server to troubleshoot high CPU usage

SQL Server to troubleshoot high CPU usage

This noon, a friend asked me to help him look at the database, the operating system is WINDOWS2008R2, the database is SQL2008R2 64-bit

64G memory, 16-core CPU

Hardware configuration is still relatively high, he said the server is running Kingdee K3 software, database instances have multiple databases

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 also high, taking up 30 g

----------------------------------------------- Gorgeous split-line ------------------------------------------------- ------

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

----------------------------------------- Gorgeous split-line ------------------------------------------------------- ----

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 you have anything to add, or the article is not correct, welcome everyone to shoot bricks!!

----------------------------------------------------------------------------------------------

2013-6-15 added, if SQL Server has resources to wait, executing the following statement will show 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

------------------------------------------------------------------------------------------------

After troubleshooting and observing these days, it is determined that some table-missing indexes are causing the indexes to be added to these tables, and the problem is solved

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_ind EX_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

SQL Server to troubleshoot high CPU usage