Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Access Database Runs Slowly Over Network Speeds Up After Compact Then Runs Slowly Again

Access Database Runs Slowly Over Network Speeds Up After Compact Then Runs Slowly Again

When your awarding is running slowly, the reflex action is to blame the database queries. It is certainly true that some of the more extravagant delays tin be adequately blamed on a missing index or unnecessary locking, but there are other potential villains in the drama, including the network and the application itself. Dan Turner points out that you could save a lot of time and coin past working out for sure where the problems lie before diving into the detail.

Slow applications affect end users first, but the impact is before long felt by the whole team, including the DBA, Dev team, network admin and the system admins looking after the hardware.

With so many people involved, each with their own view on the likely cause, it tin can be hard to pivot downward where the bottlenecks really are.

Broadly, in that location are ii primary causes of performance issues with a SQL Server awarding:

  • Network problems – relating to the speed and capacity of the "pipe" connecting your SQL application client to the database
  • Tedious processing times – relating to the speed and efficiency with which requests are processed, at cease side of the pipage.

In this commodity, we're going to look in a bit more detail about how to diagnose these and go to the bottom of performance issues.

Network bug

Network operation issues broadly break down into issues relating to the speed of responses across the network (latency) or to the capacity of the network (bandwidth) i.e. how much data it can transmit in a set time.

Of grade, the 2 are interconnected. If network traffic generated by your app (or other apps on the aforementioned network) is overwhelming the available bandwidth, this in turn could increase latency.

Latency

Latency is the time information technology takes to transport a TCP packet between the app and the SQL Server. You incur latency on the style up to the DB and on the way downward. People more often than not talk most latency in terms of round trip times: i.due east. the time to go at that place and back

Figure ane shows a 60-millisecond round trip.

C:\Users\dan\AppData\Local\Microsoft\Windows\INetCache\Content.Word\latency.png

Effigy 1

Bandwidth

The amount of data that can be sent or received in an amount of time, normally measured in kb/south or Mb/s (megabits per 2nd).

People often talk about the 'size of your pipe' when discussing bandwidth and information technology'southward a good analogy (plus it sounds naughty): the fatter your pipe, the more information you can get through it at once.

If your app needs to receive a 10-megabyte response (that's fourscore megabits!) and you have a 20 Mb/southward connexion, the response will take at least four seconds to be received. If you take a 10Mb/south connection information technology volition take at least eight seconds to exist received. If other people on your network are streaming Game of Thrones, that will reduce the bachelor bandwidth for yous to use.

Application problems: slow processing times

Whenever the client sends a request to SQL Server, to recall the required information set, the total processing time required to fulfill a request comprises both:

  • App processing time: how long information technology takes for the app to process the data from the previous response, before sending the next asking
  • SQL processing fourth dimension: how long SQL spends processing the request earlier sending the response

Figure ii provides a uncomplicated illustration of this concept.

C:\Users\dan\AppData\Local\Microsoft\Windows\INetCache\Content.Word\processing_time.png

Figure 2

Where is the time existence spent?

We spend a lot of time investigating the performance of Client/Server SQL applications, and in that location are an overwhelming number of different tools, scripts and methods to help you troubleshoot any number of different types of performance issue.

So how, when confronted with slow application response times, practise we pinpoint speedily the root cause of the problem? The flowchart in Figure 3 shows a systematic style of approaching the problem.

Figure iii

When investigating operation problems, you may have more than one upshot. It'due south worth looking at a few different parts of the app. Is it a general problem? Or are some parts much slower than others?

It'southward best to offset modest. It will make life easier if you can focus on a specific area of the app that is specially slow, for example let'south say when you click the "Select All" push button on the invoices page, it takes 10 seconds to load the results. Focusing on a small repeatable workflow will allow you isolate the event.

The next question to answer, of class, is Why is it taking 10 seconds? The showtime and easiest way to narrow the upshot downwardly is to run the app equally shut to the SQL Server as possible, on the same machine, or on the aforementioned LAN.

If having effectively removed any network latency and bandwidth constraints it suddenly takes a second or less to select all the invoices, and then you need to investigate what network problems might be eating upwards all the rest of the fourth dimension.

If the app is still taking near x seconds to load the results, and so congratulations, you've again eliminated 2 of the four bug! Now, you lot need to look at where the bulk of this processing fourth dimension is being spent.

Permit's accept a closer look at how to work out where the bulk of this fourth dimension is beingness spent. You'll need either Wireshark or SQL Profiler (whichever you're more comfortable with).

Investigating application processing times

You'll see the fourth dimension in one of 2 places: between sending a response to the app and getting the next asking (the app processing fourth dimension) or between issuing a asking to SQL Server and getting a response (the SQL processing time).

To work out which one is causing your issue you tin can use either Wireshark or SQL Profiler as both tin tell us the approximate app and SQL processing time (although the exact numbers may differ slightly).

Using Wireshark

Nosotros can use Wireshark to capture the network traffic while the workflow is executing. Using Wireshark allows united states of america to filter out non-application traffic and wait at the time difference between all the packets in the workflow.

To summate the approximate awarding processing time:

  1. Capture the packets for the workflow: Start a Wireshark capture and run the app workflow, remember to end the capture once the workflow is complete. Remember to select the relevant network interface and note that you lot'll need to run the app on a different machine from the database for Wireshark to run across the traffic. Brand sure y'all're not running any other local SQL applications other than the one you're trying to capture.
  2. Get rid of the non-app traffic by applying the filter tds and then File | Consign Specified Packets, giving a filename and making sure "Displayed" is selected. Open up this new file in Wireshark.
  3. Show the time difference between the current and previous packet, simply by adding the time delta cavalcade, as follows:
    1. Select Edit | Preferences | Appearance | Columns
    2. Click the + button, change the type dropdown to "Delta Time" and the Championship to "Delta"
  4. Filter the traffic to simply Requests:

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

    The above filter volition evidence but the first TDS packet in each request, and the Delta column volition now evidence the fourth dimension between the last response package of the previous request and the next request. Make sure the packets are ordered by the "No." column as this volition ensure that the packets are in the order they were sent/received.

  5. Export as a CSV, by navigating File | Export Package Dissections | As CSV
  6. Calculate app processing time in seconds – open the CSV in Excel and sum upwards the values in the Delta column.

To get approximate SQL processing time:

  1. Reopen the file yous created in pace 2. to a higher place in Wireshark, filter the traffic to just responses:

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

    The to a higher place filter will show only the first TDS bundle in each response, and the Delta column volition at present show the fourth dimension betwixt the last request packet of the previous request and the first response packet sent dorsum from the SQL Server. Once again, ensure the packets are ordered by the "No." cavalcade.

  2. Consign as a CSV, past navigating File | Export Bundle Dissections | As CSV
  3. Calculate SQL processing fourth dimension in seconds – open the CSV in Excel and sum upward the values in the Delta column.

Using SQL Profiler

Although collecting diagnostic data using SQL Profiler is known to add some overhead to your workflow it should however give you a wide picture of the processing times. You can minimize this overhead by running a server-side trace, and and then exporting the information as described beneath. Alternatively, if you're confident with Extended Events and XQuery, yous should be able to become like data via that road.

Start by capturing a Profiler trace of the workflow, just using the "Standard (default)" trace template. Make sure that zero else is hit the database at the same time so you're just capturing your traffic. Once you've captured the workload in the trace, save it to a trace table using File | Save Every bit | Trace Table.

In SQL Management Studio, query the table yous created with the following two queries to give you the approximate app and SQL processing times:

Investigating latency and bandwidth issues

If the application is fast when run locally, information technology looks similar you take network issues. At this betoken, you will need to know the latency between the awarding and SQL Server. You can get a crude thought of this from a ping, which will tell y'all the fourth dimension it takes to brand a round trip between the ii. Attempt and take the measurement when the network is at low load as high network load can increment ping times.

If y'all count the number of queries the application issues, you tin work out how much fourth dimension is taken by latency.

To get the number of queries from Wireshark, you tin can apply the post-obit filter and then wait at the "displayed" count in the status bar:

To become the number of queries in SQL Profiler, create a trace tabular array as described previously, and run the post-obit query:

You demand to multiply this query count by the network latency (the ping value). For case, if the application sends 100 queries and your network latency is 60ms then the total transit time is 100 * 60 = 6000ms (6 seconds), whereas on a LAN it would take 100 *1 = 100ms (0.one second).

This should tell you if latency is your upshot. If it'due south non, then you take a bandwidth issue.

What a moment though. We oasis't explicitly seen a bandwidth issue, we just ruled out the other issues. How do we confirm it? Great question. It'south a bit fiddlier I'yard agape.

If yous take a network-level device with traffic monitoring, and a defended connection to the SQL server, you can expect and see if your workflow is saturating the available bandwidth.

Alternatively, you need to look at how much bandwidth the application uses when y'all know you don't have a bandwidth bottleneck. To do this, y'all once again need to run the awarding close to the database, capture the packets in Wireshark, and examine the bandwidth used by the application. Once again, make sure you lot're not running any other local SQL applications other than the one you lot're trying to capture.

Once you lot have completed the capture in Wireshark:

  1. Use the filter: tds
  2. Click Statistics | Conversations and tick the box "Limit to display filter". You should and so see your App workflows conversation in the conversations window.
  3. The bandwidth used is shown as "Bytes A -> B" and "Bytes B -> A"

Echo the capture while running the application over the high latency network, and look again at the bandwidth used. If in that location is a large discrepancy between the 2, then you lot are probably bandwidth constrained.

Of course, for an authentic comparison, you need to be running SQL Server and the application on like hardware, in both tests. If, for example, SQL Server is running on less powerful hardware, it will generate less traffic across the network in a given time.

Root cause analysis

It'south quite possible you accept multiple problems! Nonetheless, afterwards going through the steps outlines to a higher place, you should be able to business relationship for all the time being spent to process the workflow. If the 10 seconds processing fourth dimension turns out to contain 6 seconds of SQL processing time, 3 seconds of transit time, and ane 2d application processing time, so yous know how to prioritize your investigations.

If the principal problem is slow SQL processing times, and so there is a lot of data out at that place near tuning and tracking down issues. For example, since nosotros already captured a Profiler trace, Gail Shaw's articles requite a good overview of how to find the procedures and batches within the trace that contributed most to performance problems. Also, Jonathan Kehayias'southward book is great for a deeper dive into troubleshooting mutual performance problems in SQL Server.

Conversely, if most of the fourth dimension is being spent in client-side processing, you may want to consider profiling your application lawmaking to locate the issues. There are lots of profiling tools out in that location depending on your programming language (for example, for .NET languages y'all can use ANTS from Redgate or dotTrace from JetBrains).

If you're suffering from network bandwidth issues, then you may need to limit the size of the data you're requesting. For example, don't utilise "SELECT *" when you're requesting data. Return simply the necessary columns, and use WHERE or HAVING filters to return only the necessary rows.

A very common cause of performance problems, in our feel, is running "communicative" applications over loftier latency networks. A chatty application is one that sends many duplicate and unnecessary queries, making way more than network round trips than necessary.

Often, these applications were originally developed on, and deployed to, a high-speed LAN and so the 'chattiness' never really acquired a problem. What happens though, when the data moves to a unlike location, such as to the Cloud? Or a customer on a different continent tries to access it? Or you demand to build geographically diverse disaster recovery environments? If you consider every query on a 1ms LAN will be 60x slower on a 60ms WAN, you lot can see how this can kill your operation.

In short, when writing a Client/Server application, you demand to avoid frequent execution of the same queries, to minimize the number of necessary circular trips to collect the required data. The two most common approaches to this are:

  • Rewriting the code – for example, you can amass and filter multiple data sets on the server to avoid having to brand a query per information set, although it's not always to change the application
  • Using query prefetching and caching – there are WAN optimization tools that will practise this, but they are sometimes expensive, and hard to configure to become high performance, without introducing bugs into the application

We've done a lot of research into these problems, while developing our Data Accelerator tool, and have adopted an approach that uses motorcar learning to predict what your application is about to practice, and prefetch the required information, so it's ready but in fourth dimension for when the application requests information technology.

In Decision

Make certain you work out where your trouble is before you spend a lot of time and coin on a possible solution. We've seen companies spend vast amounts of money and homo hours optimizing SQL queries, when their biggest problems take been awarding operation bug. Conversely, we've seen companies putting more and more RAM or CPUs into a SQL server when this will never make up for the extra time the network latency is adding. If you tin pinpoint where the workflow processing fourth dimension is really being spent, yous direct you time and efforts in the right mode.

Hopefully this has given yous some ideas of how to investigate your own app's operation, or start to runway down any issues you might have.

hogbenened1985.blogspot.com

Source: https://www.red-gate.com/simple-talk/development/dotnet-development/database-application-slow/