Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

SQL Server keeps track of all the reads and writes it submits to the operating system (OS).  It records this information in sys.dm_io_virtual_file_stats DMF.  We can use the information recorded there in conjunction with the when the SQL was last restarted to figure total number of Input-Output Per Seconds (IOPS) and Bytes Per Second (BPS) generated by SQL Server.

You ask… WHY? Why do I care?

Well let’s say you are having problem with disk sub system, you have multiple databases you are trying to figure out which database generated the most IO?  Or better yet which file did the most work …

You say AHA! I can just select everything virtual file stats DMF.  True, True you can, you can find out which file has the most activity.  But now you go to your SAN guy, you SAN is not good enough for my SQL Server.

SAN guy in return is going to say “Nope, I don’t see any issues…”

So now what?

This is where this information comes useful, you need to present to the SAN guy with total number of IOPS and Throughput per second SQL Server is generating.  Then ask the SAN guy can the current configuration support you IOPS workload?  Can you current HBA configuration or iSCSI configuration support the current workload?

So this is where this script comes in, up_Get_IOPS_n_BPS.  Try it out and tell me what you think, I welcome comments.

SET NOCOUNT ON

DECLARE @SQLRestartDateTime Datetime
DECLARE @TimeInSeconds Float

SELECT @SQLRestartDateTime = create_date FROM sys.databases WHERE database_id = 2

SET @TimeInSeconds = Datediff(s,@SQLRestartDateTime,GetDate())

PRINT 'Input-Output Per Second and Bytes Per Second by Database and File'
PRINT ''

    SELECT   DB_NAME(IVFS.database_id) AS DatabaseName
           , MF.type_desc AS FileType
           , MF.name AS VirtualFileName
           , MF.Physical_Name AS StorageLocation
           , ROUND((num_of_reads + num_of_writes)/@TimeInSeconds,4) AS IOPS
           , ROUND((num_of_bytes_read + num_of_bytes_written)/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS
      JOIN sys.master_files MF ON IVFS.database_id = MF.database_id AND IVFS.file_id = MF.file_id
  ORDER BY DatabaseName ASC, VirtualFileName ASC

  PRINT 'Input-Output Per Second and Bytes Per Second by Database'
  PRINT ''

    SELECT   DB_NAME(IVFS.database_id) AS DatabaseName
           , ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS
           , ROUND((SUM(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS
  GROUP BY db_name(IVFS.database_id)
  ORDER BY DatabaseName ASC

  PRINT 'Total Input-Output Per Second and Bytes Per Second on Server'
  PRINT ''

    SELECT   ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS
           , ROUND((sum(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS

  SET NOCOUNT OFF

I will be adding additional scripts for this that let you calculate IOPS generated in last hour or last day also, maybe the heavy workload happened only recently.  Buttttt… that’s for another day.

Example Output – Input-Output Per Second and Bytes Per Second by Database

Example – Total Input-Output Per Seconds and Bytes Per Seconds on Server

Update Oct 1, 2012:
Fixed the SQL Statement to get the last restart date. Added “Where database_id = 2”. Since TempDB is recreated every time, we can use that to calculate the our start up time.