A select statement causes a bottleneck

Situation: Last week, the company launched a new project. On the first day of its launch, we found a bottleneck in the network I/O between the database server and the IIS server in the background, with 1 GB of network bandwidth, it occupies 70%-100%, that is, 700 mb-1 GB of data is transmitted per second, and the memory usage of the database is as high as 21 GB.

The CPU usage of the IIS server is frequently increased to 80%-90%, leading to frequent connection timeout on the website.

 

Cause: In the evening, I had to temporarily shut down the website, perform server maintenance, and perform comprehensive inspection and tracking. It was found that a SELECT statement resulted in:

 

Select * From Table1

 

The syntax of this statement is correct, but there is a problem in the application. Table1 stores more than 0.1 million rows of data, and the table data increases by tens of thousands every day.

To count the total number of rows, this statement is frequently called and refresh should not be less than 1000 times per second.

This also leads to network bottlenecks.

 

Solution: Change the SELECT statement

 

Select count (*) from Table1

 

The problem can be solved. The network I/O data is immediately reduced to less than 10 MB, and the memory usage of the database is also kept in the estimated range of 12 GB.

 

It seems very simple, but it is not. It takes six hours to solve the problem, one hour to check the problem, and five hours to modify the code.

 

Summary:

Be careful in your work and avoid making low-level mistakes. Sometimes success depends on the details.