Database Reference
In-Depth Information
ETL Hardware Options
Additional hardware can include adding more random-access memory (RAM), additional processors, or
additional hard drives. Of the three, RAM is the most vital. Whenever SQL Server selects data from a table, it must
pull the table's data into RAM before it returns the result set. If there is insufficient RAM, SQL Server will have to
temporarily use the hard drive as a storage base, which is substantially slower than if RAM was available. While
SQL Server performs these actions quite efficiently, adding RAM will provide you with a quick and easy way to
improve your select performance.
When adding RAM, it is important to remember that a 32-bit operating system cannot access more than
4 GB of system RAM. Therefore, you should move your installation of SQL Server to a 64-bit operating system.
Adding additional processors can sometimes help SQL Server improve select performance as well. Whether
this happens is dependent upon the complexity of the SQL query and what other server software is on the same
computer as your SQL Server. Nowadays, most computers come with multiple processors. Typically, these
additional processors are more than sufficient for SQL Server's needs. If you have SQL Server, SSAS, SSRS, and
other server software like Exchange all installed on the same computer, it will take a toll. Using a single computer
with multiple processors is simpler to administer. But, remember that one computer can hold only so much
RAM, so many hard drives, and only a single motherboard. Therefore, using multiple computers provides better
performance compared to multiple processors, albeit at a greater cost.
Adding additional hard drives is a common technique for improving performance. The practice of
configuring two or more hard drives to act as a single hard drive is referred to as a Redundant Array of
Independent Disks (RAID). It is usually referred to as a
RAID array
.
The three most common types of RAID configurations are striping (RAID 0), mirroring (RAID 1), and
mirroring with striping (RAID 1+0). Figure
18-4
illustrates these three configurations.
Figure 18-4.
Three comon types of RAID configurations
