1

When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean? Specifically, how many "volumes" would be the following cases:

  1. Locally attached disk that's split into two partitions E: and F:
  2. Software RAID 1 set E: composed of 2 locally attached disks (yes I know software RAID is bad-- adding this case to help me understand SQL Server's definition of "volume", not to design a production setup!)
  3. Hardware RAID 1 set E: composed of 2 locally-attached disks
  4. SAN disk E: on who knows/who cares how many disks.
  5. 1 SQL Server filegroup spread across two locally attached disks E: and F:

I assume the answers to #3 and #4 are "1 volume" and #5 is "2 volumes" but it's #1 and #2 that I'm most curious about.

The specific reason I'm asking is wondering if it's possible to increase the Resource Governor's IOPS limit for locally-attached SSD tempdb while having a lower limit for our SAN data storage.

So I'm wondering if splitting a single physical disk into multiple partitions might be a way to do this, by putting separate tempdb files on each partition so the total tempdb If #1 above makes SQL Server treat one physical disks as multiple volumes for throttling purposes, this may be an option. I'm assuming that this won't work-- that SQL Server is smart enough to know that 2 partitions is one "volume". But was worth asking.

1

A "volume" is either a drive letter, or a raw partition in SQL Server.

For a resource pool configured for X maximum IOps per volume where a query uses tempdb, splitting that tempdb across multiple volumes on a single physical disk, SSD or not, with the data on a single volume may well give you the granularity you desire. As always, I'd test it.

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.