Industry experts will tell you that virtualization of your environment is not done to improve performance, it’s done to make it cheaper and easier to manage. The task of most VM administrators is to cram as many VMs into a farm as possible. One of the ways is to accomplish that is to allocate “thin provisioned” storage to each VM.For each VM that is created, the VM admin has to specify the number of virtual CPUs, the amount of virtual RAM, the number and size of each virtual disk, as well as a few other items. The virtual disks can be allocated in two different ways: thin provision or thick provision. The difference between thick and thin is very simple and outlined in this diagram from VMware.
Thick provisioned storage allocates all storage when the disk is created. This means if a VM admin allocate 25GB for a virtual disk, then VMDK file on the host is actually 25GB.
Thin provisioned storage allows the VM admin to essentially over allocate storage, much in the same way they can over allocate memory. For example, if a VM admin allocates 25GB for a virtual disk, then the VMDK file will start out at a few MB then grow as the space is used by the VM. However, within the VM, the Windows operating system will see the disk as having a total capacity of 25GB.
Below, you can see Windows shows both Drive E and F as 25GB in size.
However, vSphere shows the thick provisioned disk (Drive E) as 25GB, but the thin provisioned disk (Drive F) is 0GB.
- VMSTORAGETEST_4-flat.vmdk is the thick provisioned disk (Drive E).
- VMSTORAGETEST_5-flat.vmdk is the thin provisioned disk (Drive F).
Thin provisioning is a great concept for using only what you need, and not allowing you waste valuable storage. However, this can have a detrimental effect on database performance. Thin provisioned disk will auto grow the VMDK file as the VM needs more space on that disk. When VMware needs to grow the VMDK file, it will cause a delay in the VM’s disk access while it’s growing. Let’s take a look at a few examples.
Example 1 – File copy from within Windows
In this test, we’ll use ROBYCOPY to copy a 20GB folder from the C drive to the thick provisioned disk (Drive E).
ROBOCOPY C:SQL E:SQL *.* /E /NFL /NDL /NJH
Copy time of 4 min 24 sec at a rate of 82MB/sec.
Now let’s do the same copy to the thin provisioned disk (Drive F) and compare the results.
ROBOCOPY C:SQL F:SQL *.* /E /NFL /NDL /NJH
Copy time of 5 min 01 sec at a rate of 73MB/sec.
Windows is getting nearly 10MB/sec faster copy times to the thick provisioned disk (Drive E).
Example 2 – Database backup to disk from SQL Server
In this test, we’ll backup a database to each of the disks and compare the runtimes.
First, we’ll backup to the thick provisioned disk (Drive E).
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘E:AdventureWorks2012.BAK’WITH INIT;
GO
Processed 449472 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Log’ on file 1.
BACKUP DATABASE successfully processed 449474 pages in 74.125 seconds (47.372 MB/sec).
Now backup to the thin provisioned disk (Drive F).
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘E:AdventureWorks2012.BAK’WITH INIT;
GO
Processed 449472 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2012’, file ‘AdventureWorks2012_Log’ on file 1.
BACKUP DATABASE successfully processed 449474 pages in 83.285 seconds (42.162 MB/sec).
As you can see, we’re seeing similar results that we saw in our earlier test. Within SQL Server we’re getting about 5MB/sec faster backup times.
After running these tests, we can look back in vSphere to see the new size of the VMDK file for our thin provisioned disk (Drive F). You’ll see the VMDK is now showing over 24GB of used space for that file.
These simple tests reveal that thin provisioning storage within VMware can indeed impact performance. This doesn’t mean that you should thick provision storage on every VM, but it does show you how this configuration can affect Windows and SQL Server. You can equate this to the data/log file auto grow feature within SQL Server; you should right-size the virtual disk from day one the same way you should right-size your database files from day one. As I stated earlier, virtualizing your SQL Servers is done to make things cheaper and easier to manage, not to make them perform better.
