SQL Performance Optimization-Methods for locating network performance problems (DEMO) and performance optimization demo

SQL Performance Optimization-Methods for locating network performance problems (DEMO) and performance optimization demo

Recently, my colleague from the project team told me that he encountered an SQL Performance problem. He said that there were only 69 records in the table, and it took more than two minutes to execute the client, which was very unscientific. I analyzed the cause and solved it. The following is a case study of installing a table-like structure. The test is as follows:

This table has a size of 13800KB (that is, more than 13 MB), because the table saves the image to the database (the Item_Photo field is of the iamge type). This is a historical reason, and this design is not sprayed for the moment. It seems that the performance problem of this long SQL Execution time is not whether there is a problem with the IO and SQL Execution plans, but on the network data transmission time (the server and the client are located in different places, the leased line bandwidth in two locations is 6 M, but many applications, emails, and systems depend on this leased line)

sp_spaceused 'Item_Test' name rows reserved data index_size unused----------- ------------- ---------- -------------- ----------- -------------Item_Test 69 13864 KB 13800 KB 16 KB 48 KB 

To verify my idea, I tested the time on the server in 2 seconds, as shown below:

From the above we know that it took a total of 2 minutes 23 seconds to execute the SQL statement on the client. So how many bytes of data is obtained by the client and how long does the data transmission take? Can you view the DETAIL information? The answer is yes. On the SSMS toolbar, select "Include Client Statistics" or press the shortcut key SHIFT + ALT + S, and then run the SQL statement to obtain the following information.

Client Statistics contains three major parts: Query Profile Statistics, Network Statistics, and Time Statistics.
The content of these parts is easy to understand, so let's take a look.

Network Statistics (Network Statistics) Number of server roundtrips: Number of server round trips TDS packets sent from client: Number of TDS packets sent from the client (Number) TDS packets received ed from server: number of TDS packets received from the server side Bytes sent from client: number of Bytes sent from the client Bytes from server: number of Bytes received from the server Time Stattistics :( Time statistics) client processing time: Client processing time Total execution time: Total execution time Wait time on server replies: server Response Wait time

The size of the bytes sent from the client and the data received from the server are clear and clear, so the time required for sending data from the server to the client is not here, in fact, it is basically close to the Client processing time (Client processing time), we can also set the Client processing time right when the network data transmission time, from the above case, we can see that this Time took 140 seconds (140132 MS), you can be sure that this SQL Performance is slow in network data transmission, rather than slow in the database that piece (Server Processing Time ).

Let's take a look. This is a general flowchart of SQL SERVER's request receiving and data output. When the client sends a request, when the SERVER receives the last TDS packet from the client, the database engine starts to process the request. After the request is complete, it sends the data to the client. It can be seen that the client needs to process (or time) to receive the data returned by the server)

During SQL optimization, if an SQL statement has performance problems, we should analyze the problem from a global perspective, you can analyze the CPU resources, network bandwidth, disk I/O, and execution plan to help you analyze and locate the root cause of the problem, rather than when the SQL response is slow, this is a database problem. Databases cannot carry this black box.

In the database wait event, ASYNC_NETWORK_IO can reflect network performance problems from another aspect. About ASYNC_NETWORK_IO wait type:

This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.

Back to how to optimize this SQL, We can optimize it from the following aspects.

1: SQL only obtains required field data

In this case, it does not need Item_Photo field data at all, so we can modify the SQL statement and retrieve only the field data we need to avoid this problem and improve the SQL Performance, in addition, based on my experience, developers often use SELECT *, regardless of whether the data is needed or not, and take it all first. This kind of Habitual behavior is indeed not a good habit.

2: Avoid this design.

Images should be saved as files on the application server, and the database only saves the path information. This design of saving images to the database is completely brainless.

The above is a small demo to introduce you to the SQL Performance Optimization Method to locate network performance problems. I hope to help you!

Articles you may be interested in:

• MySQL performance optimization tips help your database
• MySQL Performance Optimization
• Mysql performance optimization script mysqltuner. pl
• MySQL DBA Tutorial: Mysql Performance Optimization-Cache Parameter Optimization
• MySQL Performance Optimization Path-modify the configuration file my. cnf
• Thread_cache and table_cache for MySQL performance optimization configuration parameters
• Analysis of max_connections configuration parameters for MySQL Performance Optimization
• Analysis of table_cache configuration parameters for MySQL Performance Optimization
• Rational configuration suggestions for Open_Table parameters for MySQL Performance Optimization
• Php performance optimization techniques for importing large amounts of data to mysql