How to find out which tables are most accessed or frequently used in SQL server

I am currently in a situation where I need to find out which tables are used frequently or the number of times in our application given a certain period of time. Lets say from some startDate to endDate.

The details I would like to get here are, the tableName and how many times it was accessed. Eg:-

tableName    No.Of Access
Table5        100
Table2         80
...
Tablen         n

Also if the above query or if there is any other way to check which columns in these tables are frequently used would be wonderful. The idea behind this exercise is to properly index the columns in most used tables. But I am not sure if this is the correct way to go about the optimization too. So if you guys have any better alternatives to identify how to optimize on the above, that would开发者_JAVA百科 also be great.

We are using SQL server 2005 and the application is running on .net framework 3.5 hosted on IIS. If any further details are required, let me know.

The only thing I'm aware of with anything like this type of information is sys.dm_db_index_usage_stats

Edit: It turns out that more reliable information can be gained from

select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

The difference between what the two report is covered well in this blog post.

The way I would tune a "poorly-performing" database is to first identify the problem queries, and then optimze them, rather than just trying to optimize everything all at one go.

You can use SQL Profiler to identify poorly performing queries. Depending on your system, track the appropriate RPC, SQL batch, T-SQL, or stored procedure events, filtering on events that take too long (I tend to start with > 5 seconds duration) or that perform too many reads (I start with 12800 reads, about 100MB). Track these for a suitable length of "prime time" system usage, and you'll get a sense of which parts of your database are performing poorly, and which are performing well (i.e. those that don't appear on the list).

Alternatively, ask your users (or yourself) which parts of the system are performing poorly, and check out the underlying queries.

Depending on the complexity of your system, proper indexing can be a bit of an art, as the strategy to optimize one query may degrade performance of another. You may go through several revisions before you find one that works well enough. (Which is why we call it "tuning".)