Is there a way to count SQL transactions in an ASP.NET Request/Response cycle?

Is there a way to count SQL transactions in an ASP.NET Request/Response cycle? In other words, is there some simple bit of code you can wire into an HttpModule at starts and ends counting in the events of my choice?

The background here is that I was chatting with some people about performance testing in a fairly complex app. We've got a fair bit of caching involved, but it would be nice to know just from a ballpark standpoint how many times we call out to the database from start to finish, and output that number to trace or a log or something.

Any ideas? 

7 Comments

  • A simple approach is to use the Sql Profiler and time the start/stop of a trace.

    I've also used it within larger tests to see what isn't in the cache based on the hits to the db.


  • Well, yeah, I know I can use Profile, but that's not the kind of in-app instrumentation I'm asking about.

  • I'm not sure that I understand your context, but can't you use one of these?

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

  • perhaps SMO (in SQL2K5) can work out here..
    Microsoft.SqlServer.Management.Smo has 'Statistic' classes along with Transactions..
    well worth looking into..

  • Well, nothing there that quite matches what I'm looking for, but cool stuff to keep in mind!

    Let me explain again... We all now how there's an extensive request/response lifecycle for ASP.NET pages. You can key into those events via an HttpModule. Imagine a module that started to "listen" for SQL queries in one of the early events, then in one of the last few events, saved that count somewhere. That's really what I'm after.

    If I were using the application block or something else that already wraps SQL calls, then I'd be all set because I could wire something into that, but I'm not so I need to find an alternative.

  • Hmmm... There's no way I see this is can be done easily.

    I'm guessing we're looking for a "Quick & Dirty" solution since you said there're no SqlHelper classes anywhere.
    1. create a "mySqlConnection" class that inherits from "SqlConnection".
    2. Override the Close & Open methods to count or log of whatever. IMHO, starting a session variable at the begining of the request and nulling it by the end of it is just eneugh. Then you can write it to Perfmon or log4net.
    3. And this is where the really cool part comes into play. Change the SqlConnection class to allways use your mySqlConnection class by adding this using at the top of each page/class:
    *** using SqlConnection = myNamespace.mySqlConnection; ***
    this will change all SqlConnection to mySqlConnection. It's a dirty trick, but IMHO it's your best bet.

  • hey,,
    use sql profiler for this...

    http://dotnetuncut.blogspot.com

Comments have been disabled for this content.