Is there a way to get the total running logical reads\writes per database from last restart? Trying to monitor what dbs are most used and if that changes over time.
You could use a query like this, with the caveat that it will also track backup activity as reads. I'm also not aware of a DMV that tracks logical reads in the way you want.
To account for the backups caveat, you may want to stick it in an Agent Job and log it to a table at the beginning and end of working periods, or some other cadence that makes sense.
If you have Query Store enabled on all of your databases, you could take a daily snapshot of runtime_stats to get closer to the truth. But that's a big if, and depending on your settings it may not capture all query activity.
SELECT run_date = GETDATE(), days_up = DATEDIFF ( DAY, DATEADD ( MILLISECOND, -vfs.sample_ms, GETDATE() ), GETDATE() ), database_name = DB_NAME(vfs.database_id), drive = LEFT ( mf.physical_name, 1 ), mf.physical_name, size_on_disk_mb = vfs.size_on_disk_bytes / 1024 / 1024., read_latency = (vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)), mb_read = vfs.num_of_bytes_read / 1000000., avg_mb_per_read = (vfs.num_of_bytes_read / NULLIF(vfs.num_of_reads, 0)) / 1000000., avg_read_stall = vfs.num_of_reads / NULLIF(vfs.io_stall_read_ms, 0), write_latency = (vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)), mb_written = vfs.num_of_bytes_written / 1000000., avg_mb_per_write = (vfs.num_of_bytes_written / NULLIF(vfs.num_of_writes, 0)) / 1000000., avg_write_stall = vfs.num_of_writes / NULLIF(vfs.io_stall_write_ms, 0) FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id ORDER BY size_on_disk_mb DESC;
I don't believe there's a DMV that tracks logical IO, but this is one of the database-level metrics you can derive with reasonable accuracy by tracking session_logout. This event misses long-lived connections and connections that change between databases. But for many scenarios is pretty accurate. EG
CREATE EVENT SESSION [session_logout] ON SERVER ADD EVENT sqlserver.logout( ACTION(sqlserver.database_id)) ADD TARGET package0.event_file(SET filename=N'session_logout') go
captures data like