May be this will help
answered Sep 29 '10 at 03:13 AM
Cyborg was mostly right. I have tweaked it slightly...
This line: AND IUS.[database_id] = DB_ID() is required in order to limit the results to the current dB.
The issue is that OBJECT IDs are unique in a dB but not across the entire instance. Therefore stats for a given ID may be collected from multiple databases unless we filter them out. What may be a base table in the dB of interest could be an extremely busy Stored Proc in another and greatly skew your results.
And, as has been stated by @WilliamD, this DMV is only good since the last reset. A row is added to sys.dm_db_index_usage_stats only as an index is accessed. If you want your report to show all indexes (including the ones that have NOT been accessed) you'll have to do a little extra logic to pull that data out.
The original request was to determine busy indexes/tables so this should do.
answered Sep 30 '10 at 07:08 AM
+1 to Cyborg, but I would want to find out what you mean by reads and writes. If you mean reads and writes in terms of '1 query = 1 read' then Cyborg's query is pretty much what you want.
If you mean '1 physical i/o read = 1 read' then that's more difficult, as you can't necessarily get the information exactly as you want it. You could get the number of reads performed by queries involving each table using
answered Sep 29 '10 at 03:40 AM
Matt Whitfield ♦♦
I'd want to drill down on what specifically you're looking for and why, but, in terms of measuring reads against tables, no, there's really not a way to do reliably do just that.
I'd say, that in general, it's not really a question of which tables are being accessed so much as if the access that is being done is being done efficiently. That's why most performance tuning focuses on some combination of these two sets of measures: what waits and queues are occurring on the system, and/or which queries are causing the most pain. In general, focusing on either or both of these sets of measures will arrive at what you need. While working with them, you may find the need to drill down to determine which statistics, indexes, file groups, files, etc., are being accessed, but again, with a focus on "is that access efficient" not so much "is it occurring."
But I'm honestly not sure what exactly you're going for here. So this might be a bit off in terms of advice.
answered Sep 29 '10 at 04:25 AM
Grant Fritchey ♦♦