Increasing IOPS for Microsoft SQL in Azure without Using Premium Storage
When running Microsoft SQL inside of Azure it is important to make sure that the virtual machine has enough I/O to support the workload that is expected. I/O is measured in IOPS which are going to be used when discussing performance throughout this blog. If the virtual machine does not have enough IOPS there will be a huge decrease in performance that can easily be prevented. A standard virtual machine with one data disk will only provide 500 IOPS which might not be enough for databases that are constantly used. In order to increase the number of IOPS, I am going to take advantage of disk striping by using a storage pool to create a virtual disk that consists of multiple data disks in Azure.
First, let’s start off with a virtual machine with one data disk so we can compare the results. In the Portal I created a storage account and A7 virtual machine with one data disk (caching turned off). Any virtual machine that supports 16 data disks will provide the maximum number of IOPS without using premium storage.
Currently, this one data disk might not provide enough IOPS for certain workflows but for now we will use this setup. After remoting into the virtual machine I used PowerShell to create the storage pool and virtual disk using the following commands:
- I needed to create a new storage pool to store all of the disks that are currently able to be added to the storage pool:
New-StoragePool –FriendlyName StoragePool –StorageSubsystemFriendlyName “Storage Spaces*” –PhysicalDisks (Get-PhysicalDisk –CanPool $True)
- Next, I created a variable $disks so I know the number of disks inside of the storage pool. This variable is important because it allows us to create a virtual disk with the same number of columns as disks:
$disks = Get-StoragePool –FriendlyName StoragePool -IsPrimordial $false | Get-PhysicalDisk
- The New-VirtualDisk command allows me to create one virtual disk out of all of the disks inside of the storage pool. Right now it is just creating a virtual disk out of the one data disk that is attached to the virtual machine but this will change later:
New-VirtualDisk –FriendlyName VirtualDisk -ResiliencySettingName Simple –NumberOfColumns $disks.Count –UseMaximumSize –Interleave 256KB – StoragePoolFriendlyName StoragePool
- In order to use this newly created virtual disk I have to initialize it, partition it and format it:
Get-VirtualDisk –FriendlyName VirtualDisk | Get-Disk | Initialize-Disk –Passthru | New-Partition –AssignDriveLetter –UseMaximumSize | Format-Volume –AllocationUnitSize 65536
After running these 4 commands the virtual disk is ready to be used. This process can also be done in Server Manager> File and Storage Services> Storage Pools but PowerShell is faster and makes it easy to change the number of columns which is going to be important later.
Next I installed SQLIO (which can be downloaded from Microsoft here) which is a useful free tool to determine the number of IOPS I can expect from my configuration. Inside of the SQLIO directory I changed the drive letter in param.txt to the newly created virtual disk. SQLIO is run through the command line so I created a simple batch file in the SQLIO directory with the following content:
sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam.txt
sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam.txt
sqlio -kR –s300 -fsequential -o32 -b8 -LS -Fparam.txt
sqlio -kW –s300 -fsequential -o32 -b8 -LS -Fparam.txt
The first two SQLIO commands are used to test random reads and random writes for 300 seconds with 32 outstanding requests and an 8 bit block size. The pause in between each line makes it easier to see each of the results before running the SQLIO next command. The last two SQLIO commands are used to test sequential reads and writes with almost the same parameters except instead of -frandom, I used the parameter -fsequential. It is important to run all 4 tests because they provide a fairly detailed analysis of the maximum performance that can be achieved from the current environment. When running the batch file to test the current configuration, I received the following results:
These results are around 500 IOPS because there is only one data disk attached. To increase the number of IOPS I went back to the Portal and attached another 15 data disks to this virtual machine. All of these data disks can be created inside of one storage account because the maximum IOPS per storage account is 20,000 which is well above the maximum 8,000 IOPS I can achieve on one virtual machine. Next, I opened Server Manager and navigated to File and Storage Services> Storage Pools where I detached and deleted the virtual disk and removed the current storage pool. In PowerShell I reran the PowerShell commands above to create another Storage Pool with all 16 disks. The important part of the PowerShell commands is the part of number 4 that has –NumberOfColumns $disks.Count. Columns are the number of physical disks that the data is striped over. The default the number of columns is 8 so with 16 disks this will only provide 8*500 IOPS = 4,000 IOPS. This is half of the amount I am expecting but by setting it equal to $disks.Count there will be 16 columns.
After the new Storage Pool was created I went back to the SQLIO directory and modified the param.txt to point to the new virtual disk drive and reran my batch script and achieved the following results:
Since the Storage Pool contains 16 disks each with 500 IOPS the results are close to the 8,000 IOPS expected. This is the maximum amount of IOPS I can achieve without using any premium storage. By using disk striping with Storage Pools I was able to gain 16 times more IOPS which can be very beneficial for databases that are heavily used or require a lot of IOPS to perform tasks. As long as I am using a virtual machine that supports 16 data disks, the price to gain this performance is low since I am only paying for the extra storage space. If you are trying to maximize the performance of your virtual machines make sure to always attach the maximum number of data disks and use a storage pool.