CODEWITHSUNDEEP
asp.netdatabaseperformance
csmba
csmba

Reputation: 4083

ASP.NET performance: counting SQL requests

We had huge performance problem when deploying our ASP.NET app at a customer, which had the DB sitting on a remote location.

We found that it was due to the fact that pages made ridiculous amount of individual SQL queries to the DB. It was never a problem we noticed because usually, the web and DB are on the same local network (low latency). But on this (suddenly) low latency configuration, it was very very slow.

(Notice that each sql request by itself was fast, it is the number and serial nature of the sequence that is the problem).

I asked the engineering team to be able to report and maintain a "wall of shame" (or stats) telling us for each page the number of SQL requests so we can use it as a reference. They claim it is expensive..

anyone can tell me how to be able to maintain or get such report cheaply and easily?

Upvotes: 1

Views: 223

Answers (4)

PHeiberg
PHeiberg

Reputation: 29811

The MiniProfiler (formerly known as the MVC mini profiler; but it works for all both MVC and Webforms) is a must in such a case IMO. If the code creating the database connections is well architectured it's a piece of cake to get it running for almost any ASP.NET application.

It generates a report on each rendered page with profiling stats, including each SQL query sent to the database for the request. You can see it in action on the Stack Exchange Data Explorer pages (top left corner).

Upvotes: 0

Wyatt Barnett
Wyatt Barnett

Reputation: 15673

First, check out SubSonic's BatchQuery functionality--it might help alleviate alot of the stress in the first cut without getting into material modification of your code.

You can schedule trace jobs/dumps from the SQL server's end of things. You can also run perfmon counters to see how many database requests the app is serving.

All that said, I'd try and encourage the customer to move the database (or a mirrored copy of the database) closer to your app. It is probably the cheapest solution in the long term, depending on how thick the app is.

Upvotes: 1

rick schott
rick schott

Reputation: 20617

I have had good success using this tool in the past, not sure if the price is right for you but it will uncover any issues you may have:

Spotlight on SQL Server

Upvotes: 0

Raj More
Raj More

Reputation: 48016

If you are on SQL Server, read up on Profiler.

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Running profiler from the UI is expensive, but you can run traces without the UI and that will give you what you want.

Upvotes: 3

Related Questions