Database Reference
In-Depth Information
Optimizing Application Workload
I cannot stress enough how important it is to optimize an application's workload in resolving a performance issue.
The queries with the highest number of reads or writes will be the ones that cause a great deal of disk I/O. I'll cover the
strategies for optimizing those queries in more detail throughout the rest of this topic.
Using a Faster I/O Path
One of the most efficient resolutions, and one that you will adopt any time you can, is to use drives, controllers, and
other architecture with faster disk transfers per second. However, you should not just upgrade disk drives without
further investigation; you need to find out what is causing the stress on the disk.
Using a RAID Array
One way of obtaining disk I/O parallelism is to create a single pool of drives to serve all SQL Server database files,
excluding transaction log files. The pool can be a single RAID array, which is represented in Windows Server 2012 R2
as a single physical disk drive. The effectiveness of a drive pool depends on the configuration of the RAID disks.
Out of all available RAID configurations, the most commonly used RAID configurations are the following
(also shown in Figure
3-1
):
•
RAID 0
: Striping with no fault tolerance
•
RAID 1
: Mirroring
•
RAID 5
: Striping with parity
•
RAID 1+0
: Striping with mirroring
- Light gray indicates data
- Dark gray indicates fault
tolerance information
RAID 0
Disk contains data
RAID 1
Disk contains mirror
content of data
RAID 5
RAID 0+1
Figure 3-1.
RAID configurations
