Database Reference
In-Depth Information
when saving capacity through thin provisioning.
Caution
Restoring files from backup or copying files between VMs that have Thin
Provisioned VMDKs will cause those disks to expand. Once the disks are
expanded, they do not shrink automatically when the files are deleted. Also, since
Windows 2008, if you do a Full Format on a Thin Provisioned VMDK, it will
cause the disk to inflate, as a full format will zero out each block. If you use Thin
Provisioned disks, you should select the quick format option when partitioning a
disk in Windows. We strongly recommend that you don't over-provision storage
resources to the point an out-of-space (OOS) condition could result from
unexpected VMDK growth. See VMware KB 1005418 and Microsoft KB
941961.
If you don't use Instant File Initialization, then SQL Server will zero out its data files
whenever they are created or extended. This will ensure you get optimal performance
from the data files regardless of the underlying virtual disk format. But this comes at the
cost of the time taken to zero out the file and the resulting impact in terms of storage IO
to the underlying storage. As previously discussed, using Instant File Initialization
allows SQL Server to act as part of Windows and not write a zero to a block before
data is written to it. In certain cases, there could be substantial storage efficiencies (IO
Performance and Capacity) by combining the use of Instant File Initialization, thin
provisioning, and SQL Server compression. This may be especially advantageous to
development and test environments. There can be a significant performance penalty if
you use a non-VAAI array without using SQL Instant File Initialization on Thin and
Thick Lazy Zero disks. VAAI allows the zeroing operation to be offloaded to the array
and performed more efficiently, thus saving vSphere resources for executing VMs. If
you use Thin Provisioned or Lazy Thick VMDKs without a VAAI-compatible array, the
entire zeroing operation has to be handled solely by vSphere.
If your SQL Server and environment meets the following requirements, you may want to
consider using Thin Provisioned VMDKs with Instant File Initialization and SQL Data
Compression:
The SQL Server workload will be largely read biased.
Performance from your storage during times that blocks are initially written to and
zeroed out is sufficient to meet your database SLAs.
Performance is sufficient from the capacity required when thin provisioning is
used.
You are not planning to use Transparent Data Encryption.
