What you want is SQL Profiler. Set up a log profile to
audit the counters you want and you should be able to get
a pretty comprehensive view. You can have profiler output
it's logging to a table that you could then transform into
some kind of OLAP schema if you want drill-down/drill-
across for analysis.

Note - SQL Profiler has a CPU and Disk IO hit that's more
or less unavoidable.

Cheers


J Huppatz
SQL Server DBA