0

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.

enter image description here

I am also following Brent's url here which is very good in cleaning up perfmon data.

1

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/

1
  • Suggest measuring latency also (sec/read, sec/write). If your tlogs are on a storage path that's running >10ms latency, you'll have a whole bunch of unhappy users (ideally should be under 2ms). – SQLmojoe Dec 1 '16 at 17:21

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.