What utilities can provide database hits/duration per page?

Question

SQL Server profiler is great for profiling SQL Server performance for web apps. However, when I'm testing my webapp I'd like a summary of database hits/duration per page.

Does anybody know of any utilities for giving you this kind of information?

Answer 1

If you want duration per page, I'd recommand Google Analytics.

If you want a summary of database hits (ie, you run three procedures during one page load so you want to show a count of three) then I would recommend adding auditing code to your sprocs.

Alternately (though more expensively in terms of processing) you could turn on either SQL Profiler or SQL Trace and then track the database hits that way to perform statistical analysis on them.

Answer 2

I would recommend setting a data access routine that will be used for all the site.

This routine/class/or whatever you like could log in the database or in a log all the "hits", their duration, error (is any), timeout, etc.

If you program it properly, you will be able to know how many DB hit / page load, avg(DBHit) + you will get as a free bonus the "longest SProc, shortest, more often called".

The positive side of this is that you don't need to modify any stored proc and you can have a nice little "wrapper" around your access to the DB.

For the "Duration per page", if you go with google analysis you will not be able to merge the information back with what you got on the database server. So I would recommend logging each access to a page in the DB.

Then you can infer that Page1.StartTime = getdate(), Page1.EndTime = (page2.Starttime-1 or session.log_off_time) for example. [This is a little basic but according to your environment you can improve it].