Advertisements
Home > Administration, Dynamic Management Views/Functions > Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

September 26, 2012 Leave a comment Go to comments

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

Database Name IOPS BPS
AdventureWorks2012 0.0002 8.76
DB1 0.0001 5.15
DB2 0.0001 5.61
BackupDemo 0.0002 7
DBAGoodies 0.0002 6.85
master 0.0003 9.66
model 0.0004 21.05
msdb 0.0002 10.15
tempdb 0.0001 5.52

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

IOPS BPS
0.0017 79.75

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.

Advertisements
  1. AnoopKim
    October 1, 2012 at 4:17 am

    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?

    • October 1, 2012 at 4:40 am

      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.

  2. October 1, 2012 at 1:40 pm

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

  3. BJ
    June 3, 2014 at 9:44 am

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

    • June 6, 2014 at 1:04 am

      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 :).

  4. KhannaManish
    January 7, 2016 at 8:19 am

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

    • January 7, 2016 at 8:25 am

      Your welcome :).

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

  5. chin kok kein
    October 19, 2016 at 4:41 am

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

    • October 19, 2016 at 11:53 am

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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: