Tagged as

Stats

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

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.

Share

About the Author

Red Gate Software makes ingeniously simple tools used by over 650,000 Microsoft technology professionals working with .NET (incl. ASP.NET), SQL Server, Azure, and Oracle. More than 100,000 companies use our products, including 93% of the Fortune 100.

Our philosophy is to design highly usable, reliable tools which elegantly solve the problems that developers and database administrators face every day.

I'm not sure I understand the logic.You've found a tool that (presumably) you like, and now that you're asked to pay for it, you're boycotting the entire company?Do you not agree with paying for software in general? Or is there something specific about Reflector/RedGate that leads you to this decision?

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.

Interesting debate here. I suppose one could get upset with Redgate for removing the "Free" version of the product, but at the end of the day they have to protect their investment. They paid good money for the "free" version of the control, and obviously invested more time and money on the product to improve for "commercial" release. This investment obviously provided work for programmers, to build a better tool for programmers.

Just because a product was free in the proof of concept phase, deosn't mean it should remain free.

As far as I am concerned they have not protected their investment.In fact they have done the exact opposite as I have stopped buying anything from them.

They have removed from the market a free product not a trial or proof of concept, a free product with a large community based contribution.More over they have removed all trace of the original product.