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);