Sql-server – What does “volume” mean in MAX_IOPS_PER_VOLUME

resource-governorsql serversql server 2014storage

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

Locally attached disk that's split into two partitions E: and F:
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!)
Hardware RAID 1 set E: composed of 2 locally-attached disks
SAN disk E: on who knows/who cares how many disks.
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.

Best Answer

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.

Related Solutions

Sql-server – What Are the Log File Names

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }

Sql-server – SQL Server data files and RAID

If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk.

If you wish to try gain performance benefits from segregating data between spindles then you need to split the drives into separate RAID arrays, perhaps four in R10 for data and two in R1 for logs, or three R1s for everything, data and logs spread over the three.

Unfortunately there is no fixed wisdom here, it will vary greatly depending on your application's I/O loads. Splitting data and logs makes little difference for many load patterns (though can make a massive difference for certain write heavy loads) for instance. The only general advice that is applicable without a lot more detail about the application is "bung it all on that volume on one file/data and one/logs and trust the I/O scheduler to be fairly bright".

Related Question