Database Reference
In-Depth Information
node. This is a new feature available with SQL Server 2012 AlwaysOn that wasn't
previously available (see
http://technet.microsoft.com/en-us/sqlserver/gg508768.aspx
).
More details about AlwaysOn Availability Groups and Failover Cluster Instances are
provided in
Chapter 9
,
“
Architecting for Availability: Choosing the Right Solutions
.
”
Tip
In the case where you are splitting Temp DB files out onto separate drives from
the other data files, it makes sense to also assign them to a separate IO controller.
This will optimize the path of the IOs from the database through Windows and
down to the underlying storage. We have used this as the foundation of our
AlwaysOn Availability Group example configuration in
Chapter 11
,
“
Configuring
NTFS File System Allocation Unit Size
Now that we have covered the SQL Server database layout on the file system, we need
to cover another important aspect of the database file system design: the NTFS
Allocation Unit Size (also known as Cluster Size). When you format a drive or mount
point in Windows, you have the option of choosing a different NTFS Allocation Unit
Size from the default (4KB in most cases). The NTFS Allocation Unit Size is important
because it's the smallest amount of disk space that can be used to hold a file. If a file
doesn't use the entire Allocation Unit, additional space will be consumed.
Having a small (default) Allocation Unit Size means there are many more times the
number of blocks at the file system level that need to be managed by the operating
system. For file systems that hold thousands or millions of small files, this is fine
because there is a lot of space savings by having a smaller Allocation Unit in this
scenario. But for a SQL Server database that consists of very few, very large files,
having a much larger Allocation Unit is much more efficient from a file system,
operating system management, and performance perspective.
Tip
For a SQL Server database that consists of very few, very large files, having a
much larger Allocation Unit is much more efficient from a file system, operating
system management, and performance perspective.
For the OS and Application Binary drive, keeping the default of 4KB Allocation Unit is
recommended. There is no benefit in changing from the default. If your page file is on a
separate drive from the OS, you should use a 64KB Allocation Unit size. For all SQL
Server database drives and mount points (data files, log files, and Temp DB files), we
