SQL Server query to show read and write ratios on user database tables. A useful tool to reference and utilize before database performance tuning.
--use <DATABASE_NAME>;
--go
select object_name(s.object_id) as table_name,
SUM(user_seeks + user_scans + user_lookups) as reads,
SUM(user_updates) as writes,
SUM(user_seeks + user_scans + user_lookups + user_updates) as total_io,
case
when SUM(user_seeks + user_scans + user_lookups + user_updates) > 0
then round(SUM(user_seeks + user_scans + user_lookups) * 100 / SUM(user_seeks + user_scans + user_lookups + user_updates), 0)
else 0
end as read_ratio,
case
when SUM(user_seeks + user_scans + user_lookups + user_updates) > 0
then SUM(user_updates) * 100 / SUM(user_seeks + user_scans + user_lookups + user_updates)
else 0
end as write_ratio
from sys.dm_db_index_usage_stats as s
inner join sys.indexes as i
on s.object_id = i.object_id
and i.index_id = s.index_id
where objectproperty(s.object_id, 'IsUserTable') = 1
group by s.object_id
order by total_io desc, writes desc, write_ratio desc;