New answers tagged dmv
There is no direct DMV which will tell you I/O for specific table. I mostly use sys.dm_exec_query_stats DMV which has useful information about query which is taking high Physical and logical reads. Looking at query you can see tables involved if query has multiple table it wont give accurate information but you would know what all tables generate most I/O ...
The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations. SELECT d.name as [Database], OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM ...
Below technet wiki article will help you in understanding what is meaning of column compressed_page_count http://social.technet.microsoft.com/wiki/contents/articles/25119.sql-server-what-does-column-compressed-page-count-value-signify-in-dmv-sys-dm-db-index-physical-stats.aspx
Top 50 recent answers are included