Any system tables/views to determine query volume per table

Question

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.

Answer 1

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