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 Answer
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).– SomeGuyOct 17, 2018 at 18:30
-