Thoughts on the bottleneck caused by a select statement

Situation reproduction:

The company launched a new project. On the first day of its launch, it found a network IO bottleneck between the database server and the IIS server in the background. The 1 GB network bandwidth occupied 2nd-70%, that is, data is transmitted 700 MB-1 GB per second, and the database uses up to 21 GB of memory.

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

Copy codeThe Code is as follows:
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.