0

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.

3

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;
2
  • 1
    Note sys.dm_io_virtual_file_stats.num_of_reads tracks "Physical IO", reads from the files to the page cache, not "Logical IO", which is reads from the page cache. – David Browne - Microsoft Apr 20 at 23:28
  • @DavidBrowne-Microsoft yeah, there's just not a great solution for what they're looking for 🤷‍♂️ – Erik Darling Apr 21 at 1:01
1

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

enter image description here

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.