Database Reference
In-Depth Information
per second and up. You can also put SSDs into arrays through a SAN or RAID, further increasing the performance
benefits. There are a limited number of write operations possible on an SSD drive, but the failure rate is no higher
than that from HDDs so far. There are also hybrid solutions with varying price points and performance metrics. For a
hardware-only solution, implementing SSDs is probably the best operation you can do for a system that is I/O bound.
Aligning Disks Properly
Windows Server 2012 R2 aligns disks as part of the install process, so modern servers should not be running into
this issue. However, if you have an older server, this can still be a concern. You'll also need to worry about this if
you're moving volumes from a pre-Windows Server 2008 system. You will need to reformat these in order to get the
alignment set appropriately. The way data is stored on a disk is in a series of
sectors
(also referred to as
blocks)
that are
stored on tracks. A disk is out of alignment when the size of the track, determined by the vendor, consists of a number
of sectors different from the default size that you're writing to. This means that one sector will be written correctly, but
the next one will have to cross two tracks. This can more than double the amount of I/O required to write or read from
the disk. The key is to align the partition so that you're storing the correct number of sectors for the track.
Adding System Memory
When physical memory is scarce, the system starts writing the contents of memory back to disk and reading smaller
blocks of data more frequently, or reading large blocks, both of which cause a lot of paging. The less memory the
system has, the more the disk subsystem is used. This can be resolved using the memory bottleneck resolutions
enumerated in the previous section.
Creating Multiple Files and Filegroups
In SQL Server, each user database consists of one or more data files and usually one transaction log file. The data
files belonging to a database can be grouped together in one or more filegroups for administrative and data
allocation/placement purposes. For example, if a data file is placed in a separate filegroup, then write access to
all the tables in the filegroup can be controlled collectively by making the filegroup read-only (transaction log files
do not belong to any filegroup).
You can create a filegroup for a database from SQL Server Management Studio, as shown in Figure
3-2
.
The filegroups of a database are presented in the Filegroups pane of the Database Properties dialog box.
