Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 103

In SQL Server 2008 and 2008 R2, you can start SQL Server with the -k command-line option (-k
followed by a decimal representing the maximum MB per second the checkpoint is allowed to l ush).
This option can smooth the impact of a checkpoint at the potential risk of a long recovery and
changed writing behavior. SQL Server 2012 includes a new feature called Indirect Checkpoint. The
following Alter Database statement will enable Indirect Checkpoint:
ALTER DATABASE <Database_Name>
SET TARGET_RECOVERY_TIME = 60 SECONDS
Indirect Checkpoint has a smoothing effect on the checkpoint process. If the system would normally
l ush 30K IOPS in one second, it will now write 500 IOPS over the one-minute recovery interval.
This feature can provide a huge savings, as it would take 428 10K disks coni gured in a RAID 1+0
stripe to absorb the 30,000 I/O burst. With Indirect Checkpoint you can use only eight disks. As
with any new database feature, ensure that you fully test this coni guration prior to turning it loose
in a production environment.
Many organizations use shared storage arrays and thin storage pools to increase storage utiliza-
tion. Keep in mind that storage pools amalgamate not only capacity, but also performance. We have
previously recommended that data, log, backup, and temporary database i les be isolated onto their
own physical storage. When the storage is shared in a storage pool, however, this isolation no longer
makes sense.
The current reason for separation is to protect the data, diversifying it across failure domains. In
other words, don't keep all your eggs in one basket. It is advisable to ensure that database backups
and physical data reside on different storage groups, or even different storage arrays.
Many IT groups maintain separate database, server, networking, and storage departments. It is
important that you communicate the expected database performance requirements to the storage
team so they can ensure that the shared storage arrays are designed to handle the load.
Once your application is running on a shared thin storage pool, performance monitoring is critical.
Each of the applications is intertwined. Unfortunately, many systems are active at the exact same
time — for example, end of month reporting. If your application negatively impacts other applica-
tions, it is often more benei cial and cost effective to move it out of the shared storage environment
and onto a separate system.
Partition Alignment
Windows versions prior to Windows Server 2008 did not properly align Windows Server partitions
to the underlying storage geometry. Specii cally, the Windows hidden sector is 63KB in size. NTFS
allocation units default to 4K and are always divisible by 2. Because the hidden sector is an odd
size, all of the NTFS allocation units are offset. This results in the possibility of generating twice the
number of NTFS requests.
Any partition created using the Windows 2008 or newer operating systems will automatically cre-
ate sector alignment. If a volume is created using Windows 2003 or older operating systems, it has
the possibility of being sector unaligned. Unfortunately, a misaligned partition must be recreated to
align it properly.