SQL performance optimization method for locating network performance problems (DEMO) _mssql

Recent project team colleagues told me that there was a SQL performance problem, he said the entire table has only 69 records, client execution cost two minutes, very unscientific. I helped analyze the cause and get it resolved. The following small series installs similar table structure, constructs a case, the test screenshot looks like this:

This table has 13800KB (that is, 13M), because the table saves the picture to the database (item_photo field Iamge type), this is the historical reason, does not spray this kind of design temporarily. It seems that the performance of the SQL execution time is not the problem of the IO and SQL itself execution plan, but in the network data transmission time (server and client located in different places, two lanes bandwidth 6M, but many applications, mail, systems rely on this line)

To verify my thoughts, I am on the server the native test time is 2 seconds, as shown in the screenshot below

From the above we know that the SQL statement was executed at the client, costing 2 minutes and 23 seconds. So how many bytes of data did the client get, and how long did the data transfer cost? Can you view these detail information? The answer is yes. In the SSMs toolbar, check the "Include Client Statistics" or use the shortcut key Shift+alt+s, and then execute the SQL statement, you can get the following screenshot of the relevant information.

Client Statistics contains three chunks: Query profile Statistics, network Statistics, and time Statistics.
The content of these parts is easy to understand, needless to say, then let's take a look.

Network Statistics (Network statistics) number of servers roundtrips: Server roundtrip times TDS packets sent from client: TDS packets (number) TDS sent from clients Packe TS received from server: TDS packets received from the server Bytes sent from client: number of bytes sent from clients Bytes received from server: Bytes received from servers time Stattistics: (Time statistic information) client processing time: Total execution duration of clients processing times: Overall execution time waiting on server replies: Server answer wait times

Bytes sent from the client and the size of the data received from the server are clear, and the time required for data to be sent to the client is not, in fact, nearly as close to client-side processing time as processing. We can also use the client processing time right when the network data transfer time, from the above case, we can see this time spent 140 seconds (140132 ms), can be sure that this SQL performance slow in the network data transfer, rather than slow in the database that piece (Server processing Time).

Let's take a look at the picture below this is an approximate flowchart of request receipt and data output from SQL Server, when the client sends the request, when the server receives the last TDS packet from the client, the database engine starts processing the request, sends the data to the client after the request completes, and as you can see from the diagram, Client receives server-side returned data also requires a process (or time)

We are in the process of SQL optimization, if a SQL performance problem, we should stand in a global perspective to analyze the problem, from the CPU resources, network bandwidth, disk IO, execution plan, etc. to analyze, so as to help you analyze and locate the root cause of the problem, rather than as long as the SQL response is slow, Just reflex-conditioned preconceptions: This is a database problem. The database can not keep the blame for the old.

In a database wait event, Async_network_io can reflect network performance problems from another side. About Async_network_io Wait type:

This waittype indicates which the SPID is waiting for the client application to fetch the data before the SPID can send mor E results to the client application.

So back to the question of how to optimize this SQL, we can optimize it from the following aspects.

1:sql only the required field data

Like this case, in fact, it does not need to Item_photo field data, then we can modify SQL, only to take the field data we need to avoid this problem, improve SQL performance, in addition to my experience, developers habitually use SELECT *, Regardless of whether the data is needed or not, just take it all over again, this kind of customary sex is really not a good habit.

2: Avoid the design of the brain residue

The picture should be saved as a file on the application server, and the database will only store its path information, and the design of saving the picture to the database is a purely brain-mutilation behavior.

The above is the small series through a small demo to introduce the SQL performance optimization of the positioning network performance problems, I hope to help!