DBA Case Study: how to solve the problem of 100% CPU usage

Http://www.51cto.com/art/200805/74978.htm

 

[51cto exclusive Article]This article is from the front line of DBA practice. The specific situation, detailed analysis, full playback, how to solve the process and panorama of CPU usage rate of 100%.
Background description:

A commercial portal website has a background architecture of Windows 2003 Server + SQL Server 2005 sp2 + IIS 6.0,
The website adopts the Asp.net 2.0 technology. The technical parameters of the server are 4-core Xeon (TM) CPU 3.00 GHz, 2 GB memory, and m Nic.

Fault description:

One day, the customer service department calls to reflect the slow response speed of the website. In the morning routine inspection of DBA, it was also found that the CPU usage was as high as 100% through the task manager and Performance Monitor. Under normal circumstances, the CPU usage of the website is only below 10%.

Solution Process:

Once an exception occurs, just as a doctor sees a doctor, you must first find out the cause of the problem before you can take the right medicine to solve the problem. To some extent, DBA is the database doctor. Ghost was originally a software used to prevent ARP attacks. Now the server is bound in two directions and is decisively disabled. After the software is disabled, the CPU usage immediately drops from the peak value of 100%, and then it keeps fluctuating between 50% and 100%. So far, we have completed the first step, eliminating the problem of operating system platform caused by Trojan viruses, but it is clear that the problem has not been completely solved, however, we can see that the root cause of the problem has been transferred to the application layer. To further verify this idea, the author suspends the web application services that access the database and finds that the CPU usage of the Database Service machine immediately drops to 0. At this point, we can be sure that the problem is already in the application system.

I have summarized the performance problems caused by the application system, which are mainly reflected in the index creation and use of important tables. The inefficient SQL statement execution causes the query or update process to be blocked, the deadlock is the primary cause of slow SQL Server Response and high CPU usage.

Execute sp_lock first. The returned results show that the X and IX locks do not appear, so the deadlock is eliminated.

First, the following check is performed:

SELECT TOP 5 total_worker_time, last_worker_time, max_worker_time, min_worker_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY max_worker_time DESC

It is found that the CPU usage time is astonishing, and it is aimed at the maininfotab and detialinfotab tables (these two tables are the two tables with the highest usage frequency and the highest record in the Web Application System). Obviously, the problem has been further defined. It is likely that the blocking caused by the index creation and usage of common tables causes the CPU processing capability to decline.

In order to further lock the problem to a more accurate position, I have enabled SQL Server Profiler for tracking. For example, because it is a performance test, I have selected a tuning template, such:

Click "run" to go to the detection page. After SQL Server Profiler has been running for a while, save the detection result as a TRC file. At this time, we can start to locate and analyze specific performance problems. Through the TRC file, we found that duration occurs multiple times with a value greater than 1000, objecttype is generally P (that is, the stored procedure type). Because the business logic subject of the Web application accessing the database subject runs through stored procedure, this file has already been used to implement the "exact" scheme. Obviously, the next plan should be to adjust the index and index fragmentation of the table.

Since the TRC file is available, why not let the database engine optimization consultant of SQL Server 2005 "show"? Many people prefer the tools provided by SQL Server 2005 and always like third-party tools. I think, the use of the tool is the same in different ways. The key is to help solve the problem. I ran the tool immediately and referred to its analysis report,
For example:

After selecting the TRC file to be analyzed, click "Start analysis". After a period of time, it will provide you with an optimization report. You can choose from the report. Through some comparison and research, I found that some indexes and information reconstruction provided by SQL Server Management studio are indeed reasonable. Therefore, these statements for re-indexing and static information are executed in SQL Server Management studio. After execution, the effect is obvious. The CPU usage of SQL Server 2005 has dropped to about 50% ^ on average. At this time, a piece of rock in my heart has been implemented, but the revolution has not yet succeeded. comrades still have to work hard. Then I continue to adjust the index of many important big tables and the reconstruction of static information plans, in addition, index fragmentation is reorganized, and the backup plan is changed to enable automatic re-organizing of index fragmentation after daily backup is completed, for example:

After constantly tuning and optimizing tables and indexes and reorganizing index fragments, the CPU usage of SQL Server 2005 dropped from the average 50% to about 10%.

 

The results also show that through the continuous efforts of the author, we have achieved a staged achievement to solve the CPU usage of 100%. With the further improvement of the number of website users, the database concurrency performance and throughput will face further tests.