SQL Server Cpu 100% common causes and Optimization

SQL Server Cpu 100% is not very common. It generally causes performance problems, such as blocking, connections, and I/O disks. Therefore, the usage of SQL Server is generally low. However, in some cases, the Cpu usage is 100%.

What operations does SQL Server use CPU resources in a concentrated manner? The following are common examples:

Common causes:

1. Compile and recompile

Compiling is the process in which SQL Server generates execution plans for commands. SQL Server needs to analyze what instructions are to be done, and analyze the structure of the table to be accessed, that is, the process of generating the execution plan. This process is mainly used for various computations, so the CPU usage is concentrated.

After the execution plan is generated, it is cached in the memory for reuse. But not all of them can be reused. In many cases, due to a change in the data volume or a change in the data structure, re-compilation is required for execution in the same sentence.

2. sort and aggregation)

During query, order by, distinct, avg, sum, max, and min operations are often performed. After the data has been loaded into the memory, we need to use the CPU to finish the calculation. Therefore, the CPU usage of these operations is higher.

3. Join Operation

When the statement requires two tables to be connected, SQL Server usually chooses the Nested Loop or Hash algorithm. To complete the algorithm, you need to run the CPU. Therefore, join may sometimes bring about a concentrated CPU usage.

4. The Count (*) Statement is executed too frequently.

Especially for large tables, if there are no conditions after Count (*) or the condition is not indexed, it will cause full table scan and a large number of CPU operations.

In SQL Server, CPU-related settings are limited, mainly in sp_configure. Common settings include Priority Boost and Lightweight Pooling.

However, it is quite convenient and intuitive to observe the execution plan using the sys. dm_exec_cached_plans view.

Solution:

1. During Emergency Handling, You can restart the SQL Server service. Of course, if the DB is an online DB, please be cautious about this operation.

2. Use SQL Server Profiler to capture statements, find SQL statements that consume performance, and optimize them. It can be said that SQL statements are like the performance keys of SQL Server. More than 80% of SQL Server performance problems are caused by SQL statements.

3. Optimize statements and stored procedures based on statements with the most CPU resources accumulated and stored procedures that need to be edited most frequently

--DMV is used to analyze the 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 50Qs.*

    FromSys. dm_exec_query_stats qs

    Order ByQs. total_worker_timeDesc)AsC

    CrossApply sys. dm_exec_ SQL _text (plan_handle)AsQ

Order ByC. total_worker_timeDesc

Go





--We can also find the most frequently recompiled stored procedure.

Select Top 25SQL _text.Text, SQL _handle, plan_generation_num, execution_count,

Dbid, objectid

FromSys. dm_exec_query_stats

    CrossApply sys. dm_exec_ SQL _text (SQL _handle)AsSQL _text

WherePlan_generation_num≫1

Order ByPlan_generation_numDesc

Go