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, sqlserver usually chooses nested loop or hashAlgorithm. 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 sqlserver 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  50  Qs.  *  

  From  SYS. dm_exec_query_stats Qs

  Order  By  Qs. total_worker_time  Desc )  As  C

  Cross  Apply SYS. dm_exec_ SQL _text (plan_handle)  As  Q

  Order  By  C. total_worker_time  Desc  

  Go  





  --  We can also find the most frequently recompiled stored procedure. 
  
  Select  Top  25  SQL _text.  Text  , SQL _handle, plan_generation_num, execution_count,

Dbid, objectid

  From  SYS. dm_exec_query_stats

  Cross  Apply SYS. dm_exec_ SQL _text (SQL _handle)  As  SQL _text

 Where  Plan_generation_num  &  GT;  1  

  Order  By  Plan_generation_num  Desc  

  Go 

RelatedArticle: SQL Server CPU performance check and optimization related SQL