Is there any way using Profiler, Perfmon, or a script to find how often individual tables and indexes are used? I want to break up a database with heavy read/write activity into filegroups, but I'm not familliar with the application yet and I don't know which tables are the most active.
lumigent log explorer can do this fairly easily
i seem to remember the 100 worst performing queries trace in sql 7 and you could probably create a trace to capture this.
however, what if your profiler is not running.
consider an audit table with objectname, username, in date, out date ,etc....
create a job to monitor access or triggers on each table and voilla.
I would consider monitoring system tables for this information and just capture it appropriately.
i will work on this.