I was digging through the SQL Server system views and Windows performance counters and can't find any metrics regarding query volume PER TABLE in a given database. I am looking for any type of information to gauge the usefulness of each table in a given database. Query count/row count/data size of query results, or anything along those lines would be great. Does anyone have suggestions for how to see this data per table? I could do a Profiler trace or XE session over time, and then query those results to do some grouping, but don't want the added overhead if the data is already there somewhere.
You can use statistics data by tracking the most modified ones
SELECT --into ##SQL_STATS_INFO --comment out h. columns for non-sql 2016 servers distinct (select db_name()) as Database_name ,o.name as Table_name ,c.name AS column_name ,s.name AS statistics_name ,p.last_updated ,p.rows ,p.rows_sampled ,h.range_high_key ,h.range_rows ,h.equal_rows ,h.distinct_range_rows ,h.average_range_rows ,p.modification_counter ,h.step_number --,sc.stats_column_id FROM sys.stats AS s cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) p cross apply sys.dm_db_stats_histogram(s.object_id, s.stats_id) h INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND c.column_id = sc.column_id inner join sys.objects o on o.object_id = s.object_id WHERE o.object_id > 100 and p.rows > 1000 and p.modification_counter > 100 order by o.name