Archive

Posts Tagged ‘DMF – sys.dm_io_virtual_file_stats’

Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

September 26, 2012 9 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.

Getting file usage stats

May 4, 2010 Leave a comment
SELECT db_name(database_id) AS DBName,
       FILE_ID, -- File ID 2 = Log, File Id = 1 Data
       CASE WHEN num_of_reads = 0 THEN
          0
       ELSE
          io_stall_read_ms/num_of_reads
       END AS AvgIOStallRead_ms,
       CASE WHEN num_of_writes = 0 THEN
          0
       ELSE
          io_stall_write_ms/num_of_writes
       END AS AvgIOStallWrites_ms
  FROM sys.dm_io_virtual_file_stats(null,null)

You can use this DMV to see what SQL Server thinks about the I/O stall times. I don’t rely on it 100% but it helpful to see which file is generating the most I/O or is performing the worst. However if you have Avg IO Stall time for read or write over 100 ms, I think it requires investigation. Correlate these with the Disk KPIs such as Avg Disk Sec/Read and Avg. Disk Sec/Write.

References: #9, #10, #11

%d bloggers like this: