Databases Reference
In-Depth Information
With the better performing hardware driven RAID so affordable these days you don't often see dynamic
disks in the field. Also, dynamic disks are not supported for Windows Failover Clustering so basic disks
are the only option if you're going to be building a cluster.
Best Practice
Don't convert your disks to dynamic disks unless you have a specific reason to do so.
Only basic disks are supported on a Windows Failover Cluster. Use hardware-based
RAID for better performance.
NTFS Allocation Unit Size
You specify the NTFS allocation unit size when you format a partition in windows. Tests in the Microsoft
labs indicate that 64 KB is the best size for partitions containing SQL Server data files or transaction log
files because it's the most common size of reads and writes from SQL Server. However, the performance
benefit is usually marginal, so you should only implement it on new drives or when re-formatting for
other reasons. You shouldn't request server downtime to change it. The default is 4 KB and this size is
required if you want to use the NTFS compression feature in Windows. Although NTFS compression
is not recommend for SQL Server database files it is supported for read-only data so you should bear in
mind that it requires the default NTFS allocation unit size.
Best Practice
Format the partitions holding your SQL Server data and transaction log files with an
NTFS Allocation Unit Size of 64 KB.
Disk Fragmentation
Any discussion on disks would not be complete without a discussion of fragmentation. Fragmentation
can occur in two forms with SQL Server:
Internal fragmentation occurs when data gets old and has been subject to many inserts, updates,
and deletes. This is covered in Chapter 11.
❑
External file fragmentation occurs when a file is created and the file system doesn't have
enough contiguous disk space to create the file in a single fragment. You end up with a single
file spread across multiple file fragments on the surface of the disk.
❑
An important point to consider is that SQL Server files don't become more fragmented once they have
been created. If files are created when there isn't enough contiguous free space, they are created in
multiple fragments. If the disk is defragmented (and the OS has enough space to fully defragment all
files) right after the files are created, then the files are no longer fragmented and won't ever become
fragmented.
The ideal scenario is that you have dedicated disks for your SQL Server files, can size each file correctly,
and disable autogrow. In this situation, you start with clean disks, create one or two files that aren't
fragmented, and they stay that way forever. Then you only need to deal with internal fragmentation.
The most common scenario, however, is that autogrow is enabled and is relied upon to continue
growing the database files as needed. Autogrow as a feature is great to help you avoid the emergency
Search WWH ::
Custom Search