SUM(user_lookups + user_scans + user_seeks) AS Reads,
SUM(user_updates) AS Updates
FROM sys.dm_db_index_usage_stats v
INNER JOIN sys.objects S ON S.object_id = v.object_id
WHERE type = 'U'
GROUP BY S.name
+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 `sys.dm_exec_query_stats`, but that doesn't separate out the reads by target object. You could also use the `sys.dm_io_virtual_file_stats` function to get the number of reads at the table level, and then try and apportion those using the data from Cyborg's query - but it would be very hit and miss - a scan would cause a lot more reads than a seek, for example.
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.
Cyborg was mostly right. I have tweaked it slightly... SELECT S.[name] AS [Name], SUM(IUS.user_lookups + IUS.user_scans + IUS.user_seeks) AS [Reads], SUM(IUS.user_updates) AS [Updates] FROM sys.dm_db_index_usage_stats IUS INNER JOIN sys.objects S ON S.object_id = IUS.object_id WHERE S.type = 'U' AND IUS.[database_id] = DB_ID() GROUP BY [Name] ORDER BY [Name]; 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.