Diagnose number of database calls in ASP.NET

Question

I have an ASP.NET page (Webforms) that is loading slowly, and from looking at the code it looks like the source comes from far too many round-trips to the database. A database call in an inner loop is executing around 8000 times, when we should require about an order of magnitude fewer database calls.

To verify that my code changes have the intended effect, what's the most straightforward way of logging/observing the number of database calls made during rendering of a single Page?

Edit: Our development database for this project is not currently configured to allow me to run SQL Server Profiler ("You must be a member of sysadmin fixed server role or have the ALTER TRACE permission"). I'll have a conversation with the DBA as to whether or not we can get this permission enabled for us in Dev, but in the mean time, I'm curious about any alternatives that don't require sysadmin privileges.

Answer 1

Sql Profiler is great in a pinch here. If you just need a verification that less SELECT N+1 is going on it will do the job. If you need more precise information and you are using a popular ORM you might want to check out the Hibernating Rhinos suite of profilers.

Answer 2

Look at Pex and Moles: http://research.microsoft.com/en-us/projects/pex/downloads.aspx . These are 'mocking objects' you can create an object that transparently detours calls to your database onject without messing up your code. Then inside that object you can place some logging code.

Answer 3

The Sql Server Profiler is the tool you want for this job.

Answer 4

Use the SQL Server Profiler - start recording before loading the page, stop after the page finished loading.

You can configure it to record only specific events and filter by things like the source application, process ID and more.

Compare different runs once you have a fix in place.

An alternative is to use the mvc-mini profiler in your webforms application, as detailed in this answer.