Why is my database application so slow?

Original link: https://www.red-gate.com/simple-talk/dotnet/net-performance/database-application-slow/

when your application is running slowly, the reflection operation is accusing the database of querying. There is no doubt that some of the more extravagant delays may be blamed for missing indices or unnecessary locks, but there are other potential pranks, including the web and the application itself. Dan Turner points out that you can save a lot of time and money by trying to determine where the problem lies and then sneaking into the details.

slow applications first affect the end user, but the entire team will soon feel the impact, including Dba,dev team, network administrator, and system administrator who takes care of the hardware.

With so many people involved, everyone has their own opinion, and possible reasons, it may be difficult to determine where the bottleneck is.

in general, There are two main reasons for performance problems with SQL Server applications:

L. Network problems-related to the speed and capacity of the "pipe" that connects SQL application clients to the database .

L. At the end of the pipe, the processing time is shorter than the speed and efficiency of processing requests.

In this article, we'll detail how to diagnose these problems and understand the bottom-up of performance issues.

Network problems

Network performance problems are widely decomposed into problems related to network response speed (latency) or network capacity (bandwidth), that is, how much data can be transmitted over a certain period of time.

Of course, the two are interconnected. This can increase latency if the network traffic generated by your application (or other applications on the same network) overwhelms the available bandwidth.

Lurking

latency is the application and The time that is required to send TCP packets between SQL Server. On the DB and down the road, you will incur a delay. People usually talk about the delay of round-trip time: That is the time to get there .

Figure 1 shows a round trip of 60 milliseconds.

Figure 1

Bandwidth

The amount of data that can be sent or received in a certain amount of time, usually in kb/s or MB/s (megabits per second).

when discussing bandwidth, people often talk about "pipe Size", which is a good analogy (plus it sounds naughty): The more you pipe, the more data you can get at once.

if your application needs to receive a 10 megabyte response (this is 80 megabits!). ), and you have a connection of up to MB/s, the response will take at least 4 seconds. If you have a 10mb/s connection, it will take at least 8 seconds. If other people on your network are streaming the "power" game, this will reduce the available bandwidth for your use.

Application problem: Slow processing time

whenever the client When SQL Server sends a request, to retrieve the required data set, the total processing time required to complete the request includes:

L. Application processing time: How long the application takes to process the data in the previous response before sending the next request

L. SQL processing time: The length of time that SQL handles requests before sending a response

Figure 2 provides a simple explanation of this concept.

Figure 2

Where do you spend your time?

we spend a lot of time investigating the performance of client/server SQL applications, and there are a vast majority of different tools, scripts, and methods to help you troubleshoot any number of different types of performance issues.

So when it comes to slow application response times, can we quickly pinpoint the root cause of the problem? the flowchart in Figure 3 shows a systematic approach to solving the problem.

Figure 3

When you investigate performance issues, there may be multiple issues. Worth a look at several different parts of the application. Is this a common problem? Or is it slower than others?

It's best to start small. If you can focus on a particular area of a particular slow application, you can make life easier, for example, when you click the Select All button on the invoice page , it takes 10 seconds to load the results. Focusing on a small, repeatable workflow will let you isolate the problem.

the next question, of course, is why it takes 10 seconds? The first and simplest way to narrow down the problem is to run the application as close to SQL Server as possible on the same machine or on the same LAN.

If you effectively eliminate any network latency and bandwidth throttling, it suddenly takes a second or less to select all invoices, and you need to investigate which network problems may disappear for the rest of the time.

If the application still takes 10 seconds to load the results, then congratulations, you've eliminated 2 of the 4 issues again! Now you need to see where most of the processing time is.

let's take a closer look at how to solve most of the spending during this time. You will need Wireshark or SQL Profiler (no matter how comfortable you are).

Survey Application Processing Time

you will see time between two places: the response of the sending application and the acquisition of the next request (application processing time) or the issue of The time between SQL Server request and get response (SQL processing time).

to resolve which one is causing your problem, you can use the Wireshark or SQL Profiler, because both can tell us the approximate application and SQL processing time (although the exact number may be slightly different).

Use Wireshark

we can use Wireshark captures network traffic as the workflow executes. With Wireshark, we can filter non-application traffic and see the difference between all the packets in the workflow.

Calculate approximate application processing time:

1. Crawl the workflow's packet: Start Wireshark capture and run the application workflow, remembering to stop capturing after the workflow is complete. Remember to select the relevant network interface, and note that you need to run the application on a different computer than the Wireshark database to see the traffic??。 Make sure that you are not running any other local SQL applications, not other local SQL applications that you are trying to capture.

by applying the filter TDs, and then using file | To eliminate non-application traffic, export the specified packet, provide a file name, and make sure Show is selected. Open the new file in Wireshark.

3. To display the difference between the current and previous packets, simply add the time increment column as follows:

A. Choose Edit | preferences | appearance | columns

B. Click the + button to change the Type drop-down menu to "Delta Time", changing the title to "Delta"

4. Clean up the traffic to only need to:

(tds.type = = 0x01 | | Tds.type = = 0x03 | | Tds.type = = 0x0E) && Tds.packet_number = = 1

The filters above will show only the first in each request TDS packet, the Increment column now displays the time between the last response packet requested and the next request. Ensure that the packets are sorted by the "No" column, because this ensures that the packets are in the order in which they are sent/received.

5. Export to CSV, by navigating file | Export Packet parsing | as CSV

6. Calculate the application processing time in seconds-open the CSV in Excel and summarize the values in the Delta column.

to get an approximate SQL processing Time:

1. In Wireshark, open the file created in step 2 above and filter the traffic to only the response:

Tds.type = = 0x04 && Tds.packet_number = = 1

The filters above will show only the first in each response TDS packet, the Increment column now displays the time between the last request packet requested and the first response packet sent back from SQL Server. Again, make sure that the packets are sorted by the "no" column.

2. Export to CSV, via navigation file | Export Packet Parsing | As a CSV

3. Calculate the SQL processing time (in seconds)-Open the CSV in Excel and summarize the values in the column column.

Use SQL Profiler

Although it is known to use The SQL Profiler collects diagnostic data to add some overhead to your workflow, but it can still give you a wide range of processing time. You can minimize this overhead by running a server-side trace and then exporting the data as described below. Or, if you have confidence in extended events and XQuery, you should be able to get similar data from that path.

first, by capturing the workflow's Profiler tracking, simply use the "standard (default)" Tracking template. Make sure nothing else triggers the database at the same time, so you only capture your traffic. After capturing the workload in the trace, use file | To save it to the tracking table Save As | The trace table.

in the In SQL Management Studio, use the following two queries to query the table you created to provide you with approximate application and SQL processing time:

/* Calculate Approximate SQL processing time for RPCs and SQL Batch queries*/

SELECT SUM (DATEDIFF (Millisecond, StartTime, EndTime)) as ' SQL processing time in MS '

From tracetable

WHERE EventClass in (10, 12);

--Selects the sum of the time difference between the start and end times

--For event classes (rpc:completed) and (sql:batchcompleted)

/* Calculate approximate app processing time*/

With Events

As (SELECT *

From tracetable

WHERE EventClass in (10, 11, 12, 13)

)

SELECT SUM (DATEDIFF (Millisecond, previousrow.endtime, currentrow.starttime)) as ' App processing time in MS '

From Events CurrentRow

JOIN Events Previousrow

On currentrow.rownumber = Previousrow.rownumber + 1

WHERE Currentrow.eventclass in (11, 13)

and Previousrow.eventclass in (10, 12);

--Select The sum of the time difference between an end of query event

--(either rpc:completed or sql:batchcompleted)

--and the next query starting event

--(either rpc:starting or sql:batchstarting)

Investigate latency and bandwidth issues

if the application is running locally, it appears that you have a network problem. At this point, you will need to know the latency between the application and SQL Server. You can get a rough idea from a ping, which will tell you the round trip time between the two. When the network is in a low-load state, try and measure it because high network load may increase the number of pings.

If you calculate the number of queries issued by your application, you can calculate how long the delay takes.

to get Wireshark the number of queries, you can apply the following filters, and then view the display count in the status bar:

(Tds.type = = 0x01 | | tds.type==0x03 | | tds.type = = 0x0E) && Tds.packet_number = 1

to get Number of queries in SQL Profiler, create a trace table as described above and run the following query:

SELECT COUNT (1) from Tracetable WHERE EventClass in (11,13)

you need to multiply this query count by the network delay (ping value). For example, if your application sends 100 queries, and your network latency is 60ms, the total pass time is 1 * = 6000ms (6 seconds), and on a LAN, it needs to be 0.1 seconds.

This should tell you whether the delay is your problem. If not, then you have a bandwidth problem.

After a while we did not clearly see the bandwidth problem, we just ruled out other issues. How do we confirm? Well, I'm afraid I'm a little surprised.

If you have a network-level device with traffic monitoring, and A private connection to SQL Server, you can see if your workflow is saturating the available bandwidth.

or, when you know you don't have a bandwidth bottleneck, you need to see how much bandwidth your application uses. To do this, you also need to run an application close to the database, capture the packets in the Wireshark, and check the bandwidth used by the application. Again, make sure that you are not running any other local SQL applications, not other local SQL applications that you try to capture.

Once you're done, Capture of Wireshark:

1. Using Filters: TDS

2. Click Statistics | dialog and tick the "Limit display filter" box. Then, you should see your app workflow dialog in the session window.

3. The bandwidth used is shown as "bytes A-> B" and "Byte B-> A"

Repeat the capture when you run the application on a high-latency network, and see the bandwidth used again. If there is a big difference between the two, then you may have limited bandwidth.

of course, you need to run in two Tests for an accurate comparison Applications on SQL Server and similar hardware. For example, if SQL Server is running on a hardware that is not strong, it will generate less traffic throughout the network for a given amount of time.

Root cause Analysis

Chances are you have multiple problems! However, after you complete the above steps, you should be able to use all the time to process the workflow. If the 10-second processing time is displayed as 6 seconds of SQL processing time, 3 seconds of transfer time, and 1 seconds of application processing time, you will learn how to prioritize your survey.

If the main problem is slow SQL processing time, there is a lot of information about tuning and tracking issues. For example, since we have captured profiler tracking, Gail Shaw's article provides a good overview of how to find the most contributing processes and batches for performance issues in tracking. In addition, Jonathan Kehayias's book is ideal for troubleshooting common performance issues in SQL Server.

Conversely, if you spend most of your time in client-side processing, you may want to consider profiling your application code to find the problem. Depending on your programming language (for example, for . NET language, you can use dottrace from redgate ants or jetbrains, there are many analysis tools.

If you experience network bandwidth issues, you may want to limit the size of the data that you require. For example, do not use "SELECT *" when requesting data. Only the necessary columns are returned, and only the necessary rows are returned with the where or having filter.

in our experience, a common cause of performance problems is running a "chat" application over a high-latency network. A chat application is to send many duplicate and unnecessary queries, making more network round trips than necessary.

Typically, these applications are initially in high-speed Developed and deployed on the LAN, so "chattiness" has never really caused a problem. What happens when data is moved to a different location, such as the cloud? Or are customers from different continents trying to access? Or do you need to build a geographically diverse disaster recovery environment? If you consider that each query on a 1ms LAN will be reduced by 60 times times faster on a 60ms wan, you can see how this will affect your performance.

in short, when writing client /server applications, you need to avoid frequent execution of the same queries to minimize the number of round trips necessary to collect the required data. The two most common methods are:

L. Rewrite code-for example, you can aggregate and filter multiple datasets on a server to avoid querying each dataset, although it does not always change the application

L. uses query prefetching and caching-some WAN optimization tools can do this, but they are sometimes expensive and difficult to configure for high performance without introducing errors in the application

We've done a lot of research on these issues, and we've developed data accelerator tools, and we've adopted a method that uses machine learning to predict what the application is going to do and prefetch the required data so it's ready because the application requests it.

Conclusion is

before you spend a lot of time and money on possible solutions, make sure you solve the problem. As we've seen, companies are spending a lot of money and labor time optimizing SQL queries when their biggest problems arise in application performance issues . Instead, we've seen that businesses are putting more and more memory or CPUs into SQL Servers, and doing so will never compensate for the extra time of network latency. If you can determine the true purpose of your workflow processing time, you can guide your time and effort in the right way.

Hopefully this will give you an idea of how to investigate the performance of your application or start tracking any issues you may have.

Why is my database application so slow?