SQL database High CPU Usage statement troubleshooting

mentioned above

Recently a project CPU occupied very high, in IIS set the CPU limit after the system frequently dropped, through the task manager found that the Sqlsever database consumes CPU to reach 40%--70%, for the database I also was in the deletion and modification of several operational level level, this time out the problem on the bite, Fortunately, after a variety of Baidu problem finally solved, memory is not good to use, in order to prevent forgetting the main troubleshooting methods recorded.

Find time-consuming statements

Ten    total_worker_time/execution_count as Avg_cpu_cost, plan_handle,   Execution_count,   ( SELECT SUBSTRING (text, statement_start_offset/21,      =1           2         ELSE statement_end_offset      -statement_start_offset)/2) From   sys.dm_exec_sql_text (sql_handle)) As Query_textfrom sys.dm_exec_query_statsorder by [Avg_cpu_cost] DESC

Directly copy and paste the above statement to find the most time-consuming first 10 statements.

To view the execution time of a specific statement

Switch to text mode

Use the following statement to see the execution time of a single statement

Go   Set statistics   profile on Set statistics IO   on Set statistics TIME on  go  /** Target statement to query CPU execution times **/     Go   Set Statistics profile off   Set statistics io off   Set statistics time off  go

Reason and countermeasure of time-consuming of statement

1. Too many single-table data: table, clean up outdated data

Troubleshooting two identical tables, a table of data in 7,000, another table because of the problem of inserting a large number of useless data to 300,000 +, the same statement for two tables query time is 0ms/900ms, the gap is very large.

Table 1 Execution Time table 2 execution time

2. Data tables are not indexed: Build a clustered index

Analysis of the table structure, found that no clustered index was established, so a clustered index is established for the table and the main operation fields, and the statement execution time after the clustered index is established.

3. Optimize the statement: This can only be a specific statement-specific analysis, constantly query the execution time to view the optimization results

Statement Optimization Completion Effect

The above 2, 3 are separate functions, you can see the effect is still very obvious, but did not achieve satisfactory results, but 2, 3 combined with the effect is amazing

　　

　　

SQL database High CPU Usage statement troubleshooting