Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 154

area network, to the most complex network interconnected systems in either the Internet or a global cor-
porate wide area network. In these larger, more complex, interconnected systems, much of the network
can be beyond your control and may have bandwidth or latency issues outside of acceptable limits, but
that you have no control over. In these cases you can do little more than investigate, document, and
report your findings.
There is actually very little that you can or need to do to optimize the network on the physical server.
If you have no reported problems with your network interface card (NIC), you have the latest drivers,
and you still find network I/O to be your bottleneck, then best approach to take is to optimize your
application. If you have too many requests going to a single server, then maybe you could think about
how to split some of the logic and spread the load across multiple servers, or if you have large datasets
being sent to a middle-tier server to process business logic, you could look at utilizing some of the SQL
Server 2005 features to move the business logic down to the SQL Server.
The fact that SQL Server 2005 can host the .NET CLR (Common Language Runtime) means that you
can write stored procedures and functions in C# or Visual Basic .NET, which creates new options for
you to locate business logic that could only be run on a middle-tier application server previously. This
brings its own set of caveats and challenges that are beyond the scope of this topic, but you should be
aware of the option.
DiskI/O
The other piece of I/O and the one that first comes to mind is disk I/O. With earlier versions of SQL
Server, disks were pretty simple. You had few options for what you could do with disks and in most
cases you only had a handful to deal with. Large enterprise systems have long had the option of using
SAN storage, whereas medium to large business systems have been able to use external disk subsystems
using some form of RAID and most likely utilizing a SCSI interface.
These days the landscape has changed for storage and thankfully so has our understanding of our
requirements and best practices for design and configuration.
StorageDesign
Good performance comes from good design. Although this isn't a storage design topic, awareness of the
terminology associated with modern storage products is important to be able to articulate requirements
and the reasoning behind the best practices.
Disk Drives
Fundamentally there are two types of disk drives that are available today: SCSI or ATA. SCSI has
traditionally been the choice for the enterprise and ATA for the lower end of the market and desktop
computing. The value proposition for ATA drives is low cost, low power, high capacity storage. ATA is
a great choice to meet these requirements.
SCSI drives are very high cost and have much lower capacity, but the value proposition is data integrity,
performance, scalability, reliability, and high duty cycle. Duty cycle is a storage term used to denote the
expected drive usage. A high duty cycle means that the drive is designed for constant 24-7 use.
Serial-ATA (SATA) and Serial-SCSI (SAS) are the most common new drives sold today and serial refers
to the interface to the disk drive. Fundamentally, the disks are the same as their historic parallel based
relatives (which are known as ATA, or sometimes PATA); it's just a new high-speed interface built into