0

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.

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
  • Unfortunately, the main servers I need this info from are on SQL 2005, which doesn't have those DMV, but this is good information for SQL 2008+ (with h references commented out for < 2016 as noted). – SomeGuy Oct 17 '18 at 18:30
  • Sounds like my old employer – Alen Oct 17 '18 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.