Measure DB Call Frequency From Web Application

[Logan S.]

I have an ASP.NET 2.0 Web application that connects to 6 SQL Server
databases.

I would like to know how I can get basic statistics on the communications
between this Web app and the databases. I am primarily interested in knowing
the number of times the databases are hit during a standard page usage
scenario. For example, user requests the page, then enters some data, a few
AJAX requests happen in the background as a result of the user's activity,
then the user clicks a button to "Submit" the page - at which point a full
page postback occurs. In this scenario/example, would like to know which of
the 6 databases were hit, which stored procedures were called, and which
dynamic SQL was submitted by the Web app.

Is it possible to do this from the Web app side of the db communications? Or
do I need to set up traces on each of the 6 SQL Server databases.

Thanks!

 

[MBUnit]

I have an ASP.NET 2.0 Web application that connects to 6 SQL Server
databases.

I would like to know how I can get basic statistics on the communications
between this Web app and the databases. I am primarily interested in knowing
the number of times the databases are hit during a standard page usage
scenario. For example, user requests the page, then enters some data, a few
AJAX requests happen in the background as a result of the user's activity,
then the user clicks a button to "Submit" the page - at which point a full
page postback occurs. In this scenario/example, would like to know which of
the 6 databases were hit, which stored procedures were called, and which
dynamic SQL was submitted by the Web app.

Is it possible to do this from the Web app side of the db communications? Or
do I need to set up traces on each of the 6 SQL Server databases.

Thanks!

What you need is a Test Driven Design concept on the Model. The Model
being the database, with testing tools such as Resharper, MBunit and
Gallio. That would allow the BLL business objects and DAL objects to be
tracked with their interactions with each other with some code
modification on the unit test side of those objects to gather
statistics, based on a unit test case to see what the expected results
are as you run the unit test case.

 

[Logan S.]

What you need is a Test Driven Design concept on the Model. The Model
being the database, with testing tools such as Resharper, MBunit and
Gallio. That would allow the BLL business objects and DAL objects to be
tracked with their interactions with each other with some code
modification on the unit test side of those objects to gather statistics,
based on a unit test case to see what the expected results are as you run
the unit test case.

Great idea - but this application is already built, and refactoring is
pretty much out of the question at this point. What I want to do is find the
most frequently called queries and stored procedures so I can do some
targeted/informed performance enhansements.