We are planning on migrating from SQL Server 2008R2 to SQL Server 2016. I was asked by my infrastructure team to monitor current IOPs usage of one of the SQL servers. I used the following script from Glenn Berry which:

Calculates average stalls per read, per write, and per total input/output for each database fill, but my infrastructure team mate does not seem to be satisfied. I have suggested to use Perfmon, but his reply was if using Perfmon we'll gather all the Io on the disk which will include non SQL IO.

So how can I monitor current IOPs usage in SQL Server?

      DB_NAME(fs.database_id) AS [Database Name]
    , CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(16,1)) AS [avg_read_stall_ms]
    , CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(16,1)) AS [avg_write_stall_ms]
    , CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(16,1)) AS [avg_io_stall_ms]
    , CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)]
    , mf.physical_name
    , mf.type_desc
    , fs.io_stall_read_ms
    , fs.num_of_reads
    , fs.io_stall_write_ms
    , fs.num_of_writes
    , fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
    , fs.num_of_reads + fs.num_of_writes AS [total_io]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
    ON fs.database_id = mf.database_id
    AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
  • 2
    You'll need to run the query more than once, calculate the difference in total_io and divide by the number of seconds between the snapshots. Not sure why the infrastructure team would want to exclude non-SQL IO unless you have plans to offload that IO during the migration. – Dan Guzman May 12 '19 at 10:16
  • @Dan Guzman, am planning on setting up a SQL job and collect that information on a daily basis. Calculating the difference in Total_io makes sense , but as i'll be collecting the data on a daily basis do i still need to divide by the number of seconds which equate to 86400 sc – Daniel May 12 '19 at 20:21
  • Be aware that might not be often enough to capture peaks the team might want to know. No harm in scheduling more often. – Dan Guzman May 12 '19 at 21:06
  • @DanGuzman Thank you for the expanation! – Daniel May 13 '19 at 15:21

As DMV data can wipe out for X reasons you can also calculate the same using perfmon counters like disk transfers/sec and monitor over a time or sum of disk reads/sec and disk writes/sec.

Additionally there is great post by Brent on this. Old but yet gold to understand more on IOPS. IOPS are a Scam

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.