Database Reference
In-Depth Information
Tip
When provisioning VMDKs for data files and transaction log files, we
recommend you size them to allow 20% free space, which allows for any
unforeseen required growth. There should be sufficient capacity for the predicted
workload over at least a three-to-six-month period. By right-sizing VMDKs and
holding data files and transaction log files for a reasonable period, you reduce
the management and administrative burden while at the same time optimize
overall performance and capacity consumption.
If you are proactively managing SQL Server data and transaction log files, and not using
Instant File Initialization, then the performance of your virtual machine will be the same
regardless of the virtual disk type you select. This is because SQL Server is zeroing out
the blocks first before they are used. If you enable IFI, then Eager Zero will give better
performance in terms of lower latency compared to Thick Lazy Zero or Thin, but only
when the block is first written to. All subsequent writes or access to the same block
will have exactly the same performance characteristics.
Although the aggregate amount of IO may be similar between the different virtual disk
options, Eager Zero generally provides the more predictable response times because
IOs will not be impacted by the additional write operation when data is written to a
new block. This predictability of IO response and generally lower latency is why Eager
Zero is required for the non-shared disks of a SQL Server Failover Cluster Instance.
Increased latency or poor IO performance can cause unnecessary cluster failovers
between nodes.
Tip
In the case of your backend storage devices supporting VAAI and the Write Same
primitive, the operation to zero out the blocks will have a minimal impact on
performance regardless of the timing of the operation, whether Eager Zero, Lazy
Zero, or Thin.
With the advent of VMware's VAAI and modern arrays that support it, the impact to the
environment of zeroing operations is reduced and therefore the performance impact of
using Eager Zero Thick disks is also reduced during initial provisioning. If you were
previously thinking of using Thick Lazy Zero VMDKs and you have a VAAI-capable
array that supports the Write Same primitive, we would recommend you use Thick
Eager Zero instead. This provides lower management overheads and optimal
performance. Regardless of whether you are using IFI or not, and in spite of the possible
overhead of having written zeros to a block that may not be used, we feel this is
justified for the decreased latency and increased predictability of IO responses that are
