ASP.NET performance: counting SQL requests

Question

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?

---

• We are using SQL Server 2005
• We have a mix of our own DB access layer and subsonic
• I know and use the profiler, but that is a bit manual. Asking here if there is a tip on how to automate or maybe I am just crazy?

Answer 1

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.

Answer 2

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

Answer 3

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.