SQL Performance Optimization: How to locate network performance issues

A colleague with my feedback he has encountered a SQL performance problem, he said that the full table only 69 records, client execution spent more than two minutes, it is not scientific. Want me to analyze the reason and solve. I have constructed a case based on a similar table structure, and the test is as follows

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

' Item_test '

rows Data index_size unused

-----------  -------------  ----------  -------------- ----------- -------------

Item_test 13864 KB 13800 KB (KB)

In order to verify my idea, I tested the server natively for 2 seconds, as shown below

From the above we know that it takes 2 minutes and 23 seconds for the client to finish executing the SQL statement. So how many bytes of data did the client get, and how long did the data transfer take? Can you see the detail information? The answer is yes. On the SSMs toolbar, tick "Include Client Statistics" or use the shortcut key shift+alt+s and execute the SQL statement to get the following information.

Client Statistics contains three chunks: Query profile Statistics, Network Statistics, Time Statistics.

The content of these sections is easy to understand, needless to say, so let's take a look.

Network Statistics (web statistics)

 Number of server roundtrips    :     number of server round trips 

TDS Packets sent from client    :    TDS Packets (number of packets) sent from the client 

TDS Packets received from server:    TDS packets received from the service side (number)

Bytes Sent From Client             Number of bytes sent from the client

Bytes received from server    :     /c9>   number of bytes received from the server

Time stattistics: (Temporal statistics)

Client Processing Time              Client processing time

Total Execution Time                Total Execution Time

wait&NBSP;  time   on&NBSP;  server&NBSP;  replies:< Span style= "COLOR: #006080" >&NBSP;  &NBSP;      &NBSP;  &NBSP;  &NBSP;  &NBSP;     &NBSP;  server answer wait time 

The bytes sent from the client and the size of the data received from the server are clear and unambiguous, so the time it takes for the data to be sent to the client from the servers is not, in fact, basically close to the client processing time (processing We can also put 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 on the network data transmission, rather than slow in the database that piece (Server processing Time).

Let's see, this is a general flowchart of SQL Server's request reception and data output, when the client sends the request, when the server receives the last TDS packet sent by the client, the database engine starts processing the request, and after the request is completed, the data is sent to the client, as you can see, The client receives the data returned by the server and also requires a process (or time)

We in the SQL optimization process, 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 and other aspects to analyze, so as to help you analyze, locate the root cause of the problem, and not as long as the SQL response is slow, Just a reflex: this is a database problem. The database is not the same as the old one.

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

This waittype indicates, 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 the SQL, only the field data we need to avoid this problem, improve SQL performance, and according to my experience, developers habitually use SELECT *, It's really not a good habit to get all the data you need, whether it's needed or not.

2: Avoid this brain-stump design

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

Resources:

https://www.simple-talk.com/sql/database-administration/how-come-the-hourglass-why-database-applications-slow-down.-/

SQL Performance Optimization: How to locate network performance issues