Database Reference
In-Depth Information
additional delays and additional latency.
Read/Write Bias
Just because your applications drive SQL to generate a read-biased workload doesn't
mean the underlying storage system will see a read-biased IO pattern. The reason for
this is the SQL buffer pool is likely to mask a lot of read IO if you have sized your VM
correctly. This will mean your IO patterns may be very write biased. Writes will be
going to your data files, Temp DB files, and your transaction log all at the same time.
You will need to make sure you have sufficient array write cache so you don't get into a
position of a force flush and a subsequent instance of the cache going write through,
which will significantly degrade performance. You must have sufficient numbers of
disks in the array to handle the cache flushes easily.
Caution
Be very careful when using 7.2K RPM SATA or NL-SAS disks on a traditional
RAID array, even with automated storage tiering. Overloading a SATA or NL-
SAS LUN can cause forced flush and significant periods of array cache write
through (instead of the friendly cache write back), to the point where the storage
processors may appear to freeze. Also, you may find LUNs being trespassed on
active/passive arrays, or just lots of path flip flops on active/active arrays. With
modern storage systems, including SSDs to host the active working set data and
acting as a second cache area, the chances of forced flushes may be reduced. But
you will need to ensure that your active working set doesn't increase to the point
where it overflows the caches and SSD and causes writes directly to slow tiers.
Plan Your Performance for Failure
Your storage system at some point will experience a failure. You need to ensure that
your critical SQL systems will perform at the minimum acceptable level during these
failure operations. During a disk failure in certain RAID configurations, you will have
significantly slower performance for both read and write operations; this is due to
parity calculations and the performance required for rebuilding data on replacement
disks. Disk rebuild can take a significant amount of time, and during rebuild situations
you may have a risk of multiple disk failure. The bigger and slower the disk, the longer
the rebuild.
Note
Some modern storage systems have done away with using RAID because of
the performance impact and risks introduced during disk rebuild
operations. If you are using a storage platform that has a different data
