Common causes and optimizations for SQL Server Cpu 100%

SQL Server Cpu 100% is not very common, and it is generally caused by the performance problems, such as blocking, number of connections, IO disk, and so on. As a result, the usage of SQL Server in general is relatively low. However, in some cases, there is still a case of CPU 100%.

What does SQL Server do when it uses CPU resources more centrally? The main common are as follows:

Common causes:

1. Compiling and recompiling

Compilation is the process by which SQL Server generates execution plans for instructions. SQL Server analyzes what the directive does, analyzes the table structure it is going to access, that is, the process of generating the execution plan. This process is mostly done in a variety of calculations, so the CPU uses a relatively concentrated place.

After the execution plan is generated, it is cached in memory for reuse. But not all of them can be reused. In many cases, the same sentence executes as the data volume changes, or the data structure changes, and it is re-compiled.

2. Sorting (sort) and aggregation calculations (aggregation)

In the query, often do order by, distinct such operations, will do the AVG, SUM, max, min Such aggregation calculation, after the data has been loaded into memory, it is necessary to use the CPU to finish these calculations. So the statements for these operations will have more CPU usage.

3. Table join (join) operation

SQL Server often chooses the Nested Loop or Hash algorithm when a statement requires two tables to be connected. The completion of the algorithm runs the CPU, so joins can sometimes lead to a relatively centralized CPU usage.

4. The Count (*) statement executes too frequently

In particular, the large table count (*), because after the count (*) If there is no condition, or the condition is not indexed, will cause a full table scan, will also cause a large number of CPU operations

Here in SQL SERVER, CPU-related settings are limited, mainly under sp_configure, the common settings are priority Boost and lightweight Pooling

For the execution plan observation, the use of Sys.dm_exec_cached_plans view will be more convenient and intuitive observation, is very much.

Workaround:

1. Emergency handling, you can restart the SQL Server Service, of course, if the DB is an online db, please be cautious of this operation

2. Use SQL Server Profiler to grab the statement, identify the consumption of the performance of the statement, the optimization point. It can be said that SQL statement is like the performance of SQL Server key, more than 80 of SQL Server performance problems are caused by SQL statements

3. Optimize statements and stored procedures based on accumulated statements that use the most CPU resources and stored procedures that most often require editing

--Use the DMV to analyze the statements in which SQL Server has accumulated the most CPU resources since it was started. For example, the following statement can list the top 50 names.

Select 

C.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time, C.last_elapsed_time,

Q.[Text]

From 

(SelectTop50Qs.*

 FromSys.dm_exec_query_stats QS

 OrderByQs.total_worker_timeDesc)AsC

 CrossApply Sys.dm_exec_sql_text (plan_handle)AsQ

OrderByC.total_worker_timeDesc

Go





--We can also find stored procedures that are most often recompiled.

SelectTop25Sql_text.Text 
 dbid, Objectid 
 
 Span style= "color: #0000ff;" >from sys.dm_exec_query_stats a 
 
 cross apply sys.dm_exec_sql_text (sql_handle) as Sql_text 
 
 where plan_generation_num > 1 
 
 orderby< Span style= "color: #000000;" > Plan_generation_num desc 
 
 go                 

Common causes and optimizations for SQL Server Cpu 100%