Post reply

tracking IO done by a particular DB of instance

  • May 29, 2014 at 8:47 am

    #396833

    Hi All

    Is there a way to see what is the IO load put by a particular DB on a given instance? I am not talking about the live load which can be tracked using profiler, but cumulative load, say all red/writes in last 7 days.

    I am fine if there is some system view which can give the Read/write information since the last restart of server.

    thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • May 29, 2014 at 9:24 am

    #1717394

    You can use a query such as this

    SELECT DB_NAME(fs.database_id) AS DBName ,

    mf.file_id ,

    mf.type_desc ,

    CAST(fs.io_stall AS FLOAT) AS io_stall ,

    --fs.io_stall_read_ms ,

    --fs.io_stall_write_ms ,

    CONVERT(DECIMAL(12,2),fs.io_stall_read_ms / (fs.num_of_reads +1)) AS ReadStalls_ms,

    CONVERT(DECIMAL(18,2),fs.io_stall_write_ms / (fs.num_of_writes +1)) AS WriteStalls_ms,

    fs.num_of_bytes_read ,

    fs.num_of_bytes_written ,

    fs.num_of_reads ,

    fs.num_of_writes ,

    fs.sample_ms AS UptimeSinceRestart_ms ,

    fs.size_on_disk_bytes / 1024 / 1024.0 AS size_on_disk_mb

    FROM sys.master_files mf

    CROSS APPLY sys.dm_io_virtual_file_stats(NULL, NULL) fs

    WHERE fs.database_id = mf.database_id

    AND fs.file_id = mf.file_id

    ORDER BY mf.database_id ,mf.file_id

    OPTION (RECOMPILE);

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply