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 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.