Why is my database application so slow?

Why is my database application so slow?

When your application is running slowly, the reflection operation will result in a database query. Admittedly, some of the more extravagant delays can be fairly attributed to missing indexes or unnecessary locks, but there are other potential villain roles in the play, including the Web and the application itself. Dan Tena, Dan Turner, points out that you can save a lot of time and money by identifying the problem before you dive into the details.

Slow applications first affect end users, but the entire team is quickly affected, including DBAs, dev teams, network administrators, and system administrators.

With so many people involved, everyone has a point of view and it's hard to determine where the bottleneck is.

In general, there are two main reasons for performance problems with SQL Server applications:

Network problems-related to the speed and capacity of the "pipeline" to connect your SQL application client to the "pipeline" of the database

Slow processing time-related to the speed and efficiency of processing requests, at one end of the pipeline.

In this article, we'll detail how to diagnose these problems and understand the underlying performance issues.

Network problems

Network performance issues are broadly decomposed into problems related to network (latency) or network capacity (bandwidth) responsiveness, i.e. how much data it can transmit within a set time.

Of course, the two are interrelated. This increases latency if your application (or other applications on the same network) generates more network traffic than is available.

Delay

Latency is the time that is required to send TCP packets between the application and the SQL Server. You get a delay on the way to the DB, and then you fall. People usually talk about delay time in round-trip time: The time to get there and return

Figure 1 shows a round trip of 60 milliseconds.

Bandwidth

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

People often talk about "the size of your pipe" when talking about bandwidth, which is a good analogy (plus it sounds naughty): The fatter your pipeline, the more data you can pass through it at the same time.

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 takes at least 4 seconds to receive. If you have a 10mb/s connection, it will take at least 8 seconds. If other people on your network are watching the Game of Thrones, that will reduce the bandwidth available.

Application problem: Slow processing time

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

Application processing time: How long before the next request is sent for the application to process the data of the previous response

SQL processing time: How long does SQL take to process requests before sending a response

Figure 2 provides a simple explanation of this concept.

Where do you spend your time?

We spent a lot of time studying the performance of client/server SQL applications, and there are a number of different tools, scripts, and methods to help you solve different types of performance problems.

So how do we quickly determine the root cause of a problem when faced with slow application response times? The flowchart in Figure 3 shows a systematic approach to dealing with problems.

When you investigate performance issues, you may experience more than one problem. Looking at several different parts of the application, is this a common problem? or are some parts much slower than other parts?

It is better to start from childhood. If you can focus on a particular area of a particularly slow application, it will make life easier, for example, when you click on the "Select All" button on the invoice page, it takes 10 seconds to load the result. Focusing on a small, repeatable workflow allows you to isolate the problem.

The next question to answer is, why does it take 10 seconds? The first and simplest way to narrow down the problem is to run the application to SQL Server as much as possible on the same machine, or on the same local area network.

If you effectively remove any network latency and bandwidth limitations, it suddenly takes a second or less time to select all the invoices, and then you need to investigate what network problems may be consumed in the rest of the time.

If the application still takes 10 seconds to load the results, then congratulations, you've deleted 2 of the 4 questions again! Now you need to see most of the processing time being used.

Let's take a closer look at how to figure out where to spend most of this time. You will need Wireshark or SQL Profiler (in a more comfortable way).

Investigate application processing Time

You will see the time in one of two locations: between sending a response to the application and getting the next request (Application processing time), or making a request to SQL Server and getting a response (SQL processing time).

To find out the cause of your problem, you can use Wireshark or SQL Profiler, because both can tell us the approximate application and SQL processing time (although the exact number may be slightly different).

Using Wireshark

We can use Wireshark to capture network traffic as the workflow executes. Using Wireshark allows us to filter non-application traffic and view the time difference between all the packets in the workflow.

Calculate approximate application processing time:

Capture a workflow's package: 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 this application on different machines, from database to Wireshark, to view traffic. Make sure that you are not running other local SQL applications, not other SQL applications that you are trying to capture.

Get rid of non-application traffic by applying the filter tds, then file | Export the specified package, give a file name, and make sure "show". Open the new file in Wireshark.

To display the time difference between the current and the previous package, simply add the time delta column, as follows:

Choose Edit | preferences | appearance | columns

Click the + button to change the Type drop-down to "Delta time" and the title to "Delta".

To filter the requested traffic:

(TDs. Type = = 0X01 | | Tds. Type = = 0 x03 | | Tds. Type = = 0x0E) & TDs. Packet_number = = 1

The filter above shows the first TDS package in each request, and the Delta column now displays the time between the last response packet and the next request. Make sure that the package is sorted by "No". This ensures that the packages are in the order in which they are sent/received.

Export to CSV, by navigation file | Export Package to decompose | into CSV

Calculate the processing time of the application in seconds-open the CSV in Excel and sum in the delta columns.

Get approximate SQL processing time:

Reopen the file that you created in step 2. On top of the Wireshark, the flow of the filter is simply a response:

Tds. Type = = 0x04 && TDs. Packet_number = = 1

The filter above shows the first TDS package in each response, and the Delta column displays the time between the last request package and the first response packet sent back from the SQL Server. Again, make sure that the package is by the "No" command. Column

Export to CSV, by navigation file | Export Package to decompose | into CSV

Calculate SQL processing time in seconds-open the CSV in Excel and sum in the delta columns.

Using SQL Analyzer

While collecting diagnostic data using SQL Profiler increases your workflow, it can still provide you with an overall picture of the processing time. You can minimize this overhead by running a server-side trace, and then export 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.

You first use the standard (default) tracking template by capturing the parser trace for the workflow. Make sure nothing else is accessed in the database at the same time, so you're just capturing your traffic.

/ * 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 ( ten, a ); --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 ( ten, one , One, 13 )      )  select Sum (datediff ( Millisecond, previousrow endtime, Currentrow. Starttime) AS< Span class= "crayon-h" > ' App processing time in MS ' /span> From Events currentrow JOIN Events previousrow           ON< Span class= "crayon-h" > currentrow. Rownumber = Previousrow. Rownumber + 1   where Currentrow. Eventclass in ( Span class= "crayon-h" > 11, 13 ) and previousrow. EventClass in ( ten ); --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 looks like you have a network problem. At this point, you need to know the latency between the application and SQL Server. You can get a rough idea from the ping that tells you the time it takes to travel between the two. When the network load is low, try to measure the network load and increase the ping time.

If you calculate the number of queries for an application problem, you can calculate the amount of time that is spent on the delay time.

To get the number of queries for Wireshark, 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 < Span class= "crayon-i" > < Span class= "Crayon-sy" > < Span class= "crayon-h" > to get SQL The number of queries in the profiler, create a trace table as described earlier, and run the following query: SELECT COUNT(1) from tracetable WHERE eventclass in (one,all) You need to multiply this query count by the network latency (ping value). For example, if your application sends 100 queries, and your network latency is 60ms, then the total transfer time is 0.1 * = 6000ms (6 seconds), while on the LAN it needs to be = 100ms (seconds).

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

What time. We haven't explicitly seen bandwidth issues, we just ruled out other issues. How do we confirm? Good question. I'm afraid it's a little too much trouble.

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

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

Once you have completed the capture in Wireshark:

Using filters: TDS

Click on the Statistics | dialog and tick the "Limit display filter" box. You should see your application Workflow dialog in the Conversation window.

The bandwidth used is shown as "Byte 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, you may be constrained by bandwidth.

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

Root cause Analysis

It's possible that you have multiple problems! However, after completing the above steps, you should be able to take into account all the time spent working on the workflow. If the 10-second processing time includes 6 seconds of SQL processing time, 3 seconds of transfer time, and 1 seconds of application processing time, you know 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 a profiler trace, Gail Shaw's article provides a good overview of how to discover processes and batches in tracking, which is the main cause of performance problems. In addition, Jonathan Kehayias's book is useful for a more in-depth study of common performance issues in SQL Server.Conversely, if you spend most of your time on client-side processing, you might want to consider profiling your application code to locate the problem. There are many analysis tools based on your programming language (for example, for). NET language, you can use an ant from Redgate or a dottrace from JetBrains.

If you are experiencing network bandwidth problems, you may want to limit the size of the data requested. For example, do not use "SELECT *" When you request data. Only the necessary columns are returned, and only the necessary rows are returned with the Where or filter.

In our experience, a common cause of performance problems is running the "chatty" application on a high-latency network. A chattering application can send many repetitive and unnecessary queries, making more network round trips more than necessary.

Typically, these applications were originally developed and deployed to high-speed LANs, so "chattiness" has never really caused a problem. What happens when the data is moved to a different location, such as the cloud? Or is a customer from a different continent trying to access it? Or do you need to build geographically diverse disaster recovery environments? If you consider that every query on a 1ms LAN will slow down 60x on the 60ms Wan, You'll see how this can ruin your performance.

In short, when writing a client/server application, you need to avoid frequent execution of the same query to minimize the number of round trips required to collect the required data. Two of the most common methods are:

Rewrite code--for example, you can aggregate and filter multiple datasets on the server to avoid generating queries in each dataset, although the application is not always changed

Use 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 bugs in the application

In developing our data accelerator tools, we have done a lot of research on these issues and have adopted a method of using machine learning to predict what your application is going to do and prefetch the required data so that it can be prepared in a timely manner when the application requests it.

Anyway

Before you spend a lot of time and money on a possible solution, make sure your problem is resolved. We've seen companies spend a lot of money and hours optimizing SQL queries when their biggest problem is application performance issues. Instead, we see companies putting more and more RAM or CPUs into SQL Servers, which will never compensate for the extra time that network latency increases. If you are able to determine where the workflow processing time really is spent, you will guide your time and work in the right way.

Hopefully this gives you some idea of how to investigate the performance of your own applications, or start tracking the problems you might have.

Why is my database application so slow?