We have a LANDEsk migration going on where several servers are being consolidated into one SQL 2014 Cluster. What would be the best way to calculate IOPS for such a scenario? The data is still being migrated.
I am also following Brent's url here which is very good in cleaning up perfmon data.
Best Answer
You have 2 options, perfmon or get the information directly from SQL. Perfmon is point-in-time, SQL's stats are aggregated since the last service restart.
For Perfmon, you'll want to use Physical Disk and provide them the following information:
Disk Reads/Sec + Disk Writes/Sec = IOPS
Disk Reads Bytes/Sec + Disk Writes Bytes/Sec = Throughput
Make sure you have enough data to fully represent your workload. For instance, if you have off-hours processes that run, like a big ETL process that runs overnight or on the weekend, make sure those are captured as well. Your storage team can't spec for something if they don't know the need exists.
You can also get this information directly from SQL. Remember this aggregated, so you'll need to take periodic snapshots and calculate the diffs. The one advantage with this method is you can break down the Stats by Database. You may find that one database has much higher IOPS/Throughput needs and this will give your Storage Admins more information in case they want to deal with it differently.
This article is a good starting point on how to capture snapshots of virtual_io_stats: https://www.mssqltips.com/sqlservertip/1416/gather-io-statistics-down-to-the-sql-server-database-file-level/