How can I monitor current IOPs usage in SQL server

Question

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?

SELECT
 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
OPTION (RECOMPILE);

• Dan Guzman: 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.
• Daniel: @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
• Dan Guzman: Be aware that might not be often enough to capture peaks the team might want to know. No harm in scheduling more often.
• Daniel: @DanGuzman Thank you for the expanation!

Answer 1

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

Answer 2

Ideally, there should be nothing else running on the server that would be generating any significant IO. Your suggestion to use Perfmon is a decent option in that case.

The DMV can give you very granular file-level IO metrics which is useful. If you want to monitor the current values you need to take a snapshot of the DMV and do a diff against the snapshot to get the IO activity for that time period. If you want a tool to do this for you, I created DBA Dash which uses sys.dm_io_virtual_file_stats for its IO metrics. The tool is free and open source and will collect lots of other useful performance data.

It's worth noting that mainstream support for SQL 2016 has already ended. It might be worth considering SQL 2022 or 2019.