2

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.

Ryan
  • 9,918
  • 7
  • 42
  • 57

4 Answers4

2

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.

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • We use SQL Server Profiler extensively for this type of analysis. Great tool – NotMe Feb 14 '12 at 22:29
  • See my edit above, but is there a way to run this (rather basic) profiling step without ALTER TRACE permissions? – Ryan Feb 15 '12 at 20:54
  • @Ryan - I don't believe so. Take a look at the mini-profiler, as an alternative. – Oded Feb 15 '12 at 21:02
0

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

JSR
  • 6,106
  • 2
  • 20
  • 24
0

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.

deadlyvices
  • 873
  • 6
  • 18
  • Please explain how Pex and Moles would help with this? These are testing aids that are not related to his problem. – Oded Feb 14 '12 at 22:06
  • You can mock the calls you are making to the database objects. He must be calling an API of some kind. I'm coming at it from the point of view of intercepting certain API calls. – deadlyvices Feb 14 '12 at 22:11
  • That's an assumption on your side. It is entirely possible that the code-behind is making the calls directly (or that there is no code-behind). – Oded Feb 14 '12 at 22:16
  • Yes I know it is, but may well be the opposite case. And please could you moderate your tone: you're coming across as rather abrupt and imperious? Thank you. – deadlyvices Feb 14 '12 at 22:22
  • And, while we're on the subject of 'assumptions', where does he say he's using SQL Server as his database? It could be Oracle or even Access. – deadlyvices Feb 15 '12 at 07:01
0

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.

Wyatt Barnett
  • 15,573
  • 3
  • 34
  • 53