Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

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

11 comments

1. SELECT @SQLRestartDateTime = create_date FROM sys.databases

   What does this returns .. the last entry of the last created databases….
   how is that good for SQLRestartDateTime?

   Reply

   1. You are correct. This does not. It should have a where clause checking for tempdb. Good catch. I’ll fix when I get to a computer.

      Reply

2. Fixed. Sorry for the mistake, AnoopKim good catch again. Thanks :).

   Reply

3. or you can always get the correct start time for SQL Server by querying sys.dm_os_sys_info DMV for ‘sqlserver_start_time’

   Reply

   1. Thanks for the tip. Didn’t consider looking in that DMV. I think these values are generally close the time SQL Starts to time TempDB created is usually with in matter of seconds, if these times are too far apart. Then maybe we have issue at SQL Server startup. Thanks again for tip :).

      Reply

4. Was looking into setting up perfmon but then this script came handy to get IOPs to date since SQL restart. Ty

   Reply

   1. Your welcome :).

      You can use this with deltas to understand what is generating IO if your system is having IO issues :).

      Reply

5. Hi, Is the IOPS measure in Thousands?
   0.3615 = 3,615(three thousand six hundred and fiftheen)?

   Reply

   1. IOPS represent each call SQL Server makes. Therefore 3615 is total number of reads and write requests made. No division are needed.

      Reply

6. I have started a new role and I see that none of the disks for SQL Server tempdb, mdf, ndf and ldf files are on disk which have been formatted as 64k. Will this script help me prove that their performance is suffering because of this? I want to recommend a phased approach of reformatting the drives but I need to have solid evidence.

   Note that this thread suggests it is not as much an issue that I believe but wondering how your code could shed light on this issue. And THANKS for sharing it!!

   64KB disk formatted vs 4KB disk formatted – SQLServerCentral Forums

   Reply

   1. Hi John,
      The discussion on SQLServerCentral is valid. The performance impact of 64 KB versus 4 KB allocation units is not particularly difficult to demonstrate, and Microsoft has long recommended formatting SQL Server data, log, and TempDB volumes with a 64 KB allocation unit size. The most reliable way to validate any performance impact in a given environment is through controlled testing, typically using disk I/O simulation tools such as SQLIO or similar utilities. [SQL Server…soft Learn | Learn.Microsoft.com], [Storage: P…soft Learn | Learn.Microsoft.com]
      I/O statistics can help indicate whether disks are underperforming, but it’s important to note that 64 KB allocation size is not always the root cause of performance issues. Historically, it was an easy, low-risk optimization that often provided measurable gains on physical servers. In virtualized environments—particularly with VMDK layers and shared storage—it has become much harder to isolate and conclusively prove its impact end to end.
      If we step back and think about what 4 KB versus 64 KB actually means, we’re really talking about the minimum allocation unit on disk. A 4 KB allocation size can reduce wasted space when a volume contains a large number of small files. However, SQL Server data files, log files, and TempDB files are typically large and sequential in nature, so small-file efficiency is rarely a concern. That is why 64 KB has remained the standard recommendation: even a modest performance improvement is still a net win for SQL Server workloads.
      More importantly, disk performance issues can originate from many layers in the stack—poor query patterns, memory pressure, or misconfiguration anywhere along the I/O path (logical disk configuration, VMDK layout, HBA, host, fabric switches, or SAN settings). Any one of these can introduce bottlenecks. In that context, 64 KB formatting has become the “default” optimization because it is simple, low effort, and historically beneficial, not because it is a universal fix.

      Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.