Understanding why an application performs badly can be a tedious process. With so many possibilities, it’s difficult to know where to start, especially if you’re
working on an ASP.NET application that relies on database queries. The latest version of ANTS Performance Profiler provides you with all the contextual
information you need to identify the bottleneck.
Let’s put ANTS Performance Profiler to the test by profiling a sample ASP.NET MVC application called NerdDinner. For the purpose of this walkthrough,
Jeremiah Peschka has modified the original NerdDinner application from
CodePlex, so it now contains performance issues. You can download
our modified version here. The NerdDinner site includes several pages that rely heavily on database queries, as well as some static HTML.
Users have reported that the site's location search functionality is slow. The search functionality is designed to return a list of events near a
certain location. To investigate, let’s launch ANTS Performance Profiler and take a look at what’s going on behind the scenes in NerdDinner.
ANTS Performance Profiler will automatically launch NerdDinner in Internet Explorer. Now that we have the application up, running, and being profiled, we can start investigating
the problem with the search, by typing a place name into the search bar and hitting ‘Search’.
While we use the site, ANTS Performance Profiler’s timeline shows the application’s CPU usage:
We can see that the CPU usage grows to almost 200% (100% for two CPU cores), and remains high even after the first results were returned. This is a clear indicator of a
performance bottleneck, so let’s explore the results in ANTS Performance Profiler.
At the top of the call stack, ANTS Performance Profiler shows the code that contributes the greatest proportion of CPU time. In this case, we can see that the .NET methods
triggered by the SearchByLocation HTTP request are at the top, accounting for almost 95% of the total time spent during the profiling session.
Looking down the highlighted hot stack, which shows the most time-consuming stack trace in the selected region, we can see that this request
called a .NET method,
NerdDinner.Models.DinnerRepository.NearestDinners(double latitude, double longitude). This method was hit nearly 4000 times - as was the SQL
SELECT query it ultimately runs.
If we select the method’s parent,
NerdDinner.Controllers.SearchController.SearchByLocation(float latitude, float longitude),
we can view its source code. This shows us that the method retrieves the full list of all recorded dinner events from the database:
NerdDinner.Models.DinnerRepository.FindByLocation(float latitude, float longitude) then tries to process the result set in the web
page, to filter by location:
These methods are good candidates for optimization. The same results could be achieved via AJAX calls, and by returning from the database dinners
that meet specified latitude and longitude criteria only.
Switching to the Database Calls view, we can see that the query which returns the full results set was run thousands of times. Altogether
the site spent over 30 seconds just waiting for instances of this query to return their first result:
Again, it's clear that this very broad request is being run repeatedly, contributing to a large total running time. It would be more efficient to run a more precise request fewer times.
After profiling, we now have a clear idea of which HTTP requests are associated with slow performance, and which of our .NET methods contain the source of those slowdowns. We have a
clear understanding of how the .NET code is calling the database, and how the database is performing. Having access to this data in one tool means we can
quickly see which methods to rewrite to remove bottlenecks, and the steps we'll need to reproduce in the application to check that the problem has gone.
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.