Click here to Skip to main content
13,446,005 members (37,649 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 18 Oct 2011

.NET Code or SQL – Where’s the Performance Problem?

, 18 Oct 2011
Identifying an application’s performance bottlenecks is all about understanding how the application is spending its time. Only with the most complete understanding of how an application is operating is it possible to understand what it is doing wrong, as this article describes.

Editorial Note

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Many modern applications rely on a database back-end, and for those building for the Microsoft stack, SQL Server is the data layer of choice. While in many cases the performance of an application will be CPU-bound, often bottlenecks turn out to lie with the application’s database interaction. This makes understanding the code’s SQL activity vital for getting a hold on its performance.

One approach used in the past has been to run the SQL Server Profiler at the same time as a separate performance profiler. This lets you understand code performance and look at SQL activity separately, but understanding how the two are related can be difficult and time-consuming. Sometimes slow or repeated queries are OK and don’t need optimizing, but it all depends on the context in which whose queries were run.

Profiling Cubed in ANTS Performance Profiler builds SQL profiling into the same performance profiler used for analyzing .NET code performance. Results can be compared side by side, with SQL and code performance data automatically matched up against each other on the interactive timeline.

Why would you want SQL data in your code profiler?

Let’s look at an example. The Beer House is an ASP.NET website using SQL Server as the data layer. After attaching ANTS Performance Profiler, detailed performance data is collected. Using the interactive timeline, we can select any period to analyze code performance, drilling down to view line-level timing data for any method. While this approach is immensely useful for finding CPU-related slowdowns, it is less helpful when the database is the bottleneck. In Figure 1, the btnVote_Click() method is taking a long time to run but, after looking at the timing in the source code window, this doesn’t seem to be because of the performance of the code itself.


Figure 1. Code performance data, including line-level timing

After selecting btnVote_Click(), and switching to the SQL Server data tab, we can see all database activity on the local server for that same timespan. We can examine which queries were run and get timing information and the number of rows returned. This helps identify occasions where queries are being run unnecessarily, often to re-fetch data which might better have been cached, or where the same query is run multiple times in quick succession by mistake.


Figure 2. Detailed SQL activity for the selected timespan

To see SQL profiling in action, watch this short video.

SQL profiling is part of Profiling Cubed in ANTS Performance Profiler, which brings together SQL, File I/O, and .NET code profiling into the same tool for the most complete understanding of your application’s performance.

Learn more about Profiling Cubed, or download you free 14-day trial.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Redgate Software
Red Gate Software Ltd.
United Kingdom United Kingdom
This member doesn't quite have enough reputation to be able to display their biography and homepage.
Group type: Organisation

1 members

You may also be interested in...


Comments and Discussions

QuestionAppealing but no thanks Pin
Pascal Ganaye18-Oct-11 8:21
memberPascal Ganaye18-Oct-11 8:21 
AnswerRe: Appealing but no thanks Pin
cdkisa18-Oct-11 14:16
membercdkisa18-Oct-11 14:16 
GeneralRe: Appealing but no thanks Pin
Jacek Gajek20-Dec-11 2:58
memberJacek Gajek20-Dec-11 2:58 
AnswerRe: Appealing but no thanks Pin
John Brett18-Oct-11 21:26
memberJohn Brett18-Oct-11 21:26 
GeneralRe: Appealing but no thanks Pin
Pascal Ganaye18-Oct-11 22:00
memberPascal Ganaye18-Oct-11 22:00 
Perhaps my logic is flawed.

I am not against paying for software at all.
I used to buy RedGate software. Or more exactly I asked my boss to buy several profiler licenses in the past. My company also bought a number of SQL tool licenses.

It is easy to ask your boss to buy a profiler, you just have to wait for him to tell you your application is slow.

It is hard to ask your boss for a disassembler.

Before RedGate decided to buy Lutz Roeder product, the product was free and extensible.

Originally what they did was fine. They acquired the product, and added a Pay version with extra feature.

I understand that and to me it is still moral.

What I believe is a betrayal is to remove the original software from the market and remove all places where you could download it.

In my mind it is wrong to buy a free product and remove it from existence.

Again: It is hard to ask your boss for a disassembler.

They also upset me with the way they handled (their forum was censored multiple times).
Being upset I reply with what I can and this is by refusing to buy from them.

Like any embargo it is unfair and not the best solution but deep down I just feel what they have done is wrong.

modified 19-Oct-11 4:51am.

GeneralRe: Appealing but no thanks Pin
BrianBissell25-Oct-11 9:02
memberBrianBissell25-Oct-11 9:02 
GeneralRe: Appealing but no thanks Pin
radioman.lt25-Nov-11 2:38
memberradioman.lt25-Nov-11 2:38 
AnswerRe: Appealing but no thanks Pin
Three Nine Consulting15-Dec-11 22:24
memberThree Nine Consulting15-Dec-11 22:24 
GeneralRe: Appealing but no thanks Pin
Pascal Ganaye16-Dec-11 3:15
memberPascal Ganaye16-Dec-11 3:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180314.2 | Last Updated 18 Oct 2011
Article Copyright 2011 by Redgate Software
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid