Database Reference
In-Depth Information
Hardware and Network
As a first step in troubleshooting, it is beneficial to look at the SQL Server hardware and network configuration.
There are several aspects of this involved. First, it makes sense to analyze if the server is powerful enough to handle
the load. Obviously, this is a very subjective question, which often cannot be answered based solely on the server
specifications. However, in some cases, you will see that the hardware is clearly underpowered.
One example when this happens is with systems developed by Independent Software Vendors (ISV) and
deployed in an Enterprise environment. Such deployments usually happen in stages. Decision makers evaluate
system functionality under a light load during the trial/pilot stage. It is entirely possible that the database has been
placed into second-grade hardware or an under-provisioned virtual machine during trials and stayed there even after
full deployment.
SQL Server is a very I/O intensive application, and a slow or misconfigured I/O subsystem often becomes a
performance bottleneck. One very important setting that is often overlooked is partition alignment. Old versions
of Windows created partitions right after 63 hidden sectors on a disk, which striped the disk allocation unit across
multiple stripe units in RAID arrays. With such configurations, a single I/O request to a disk controller leads to
multiple I/O operations to access data from the different RAID stripes.
Fortunately, partitions created in Windows Server 2008 and above are aligned by default. However, Windows
does not re-align existing partitions created in older versions of Windows when you upgrade operating systems or
attach disks to servers. It is possible to achieve a 20-40 percent I/O performance improvement by fixing an incorrect
partition alignment without making any other changes to the system.
Windows allocation unit size also comes into play. Most SQL Server instances would benefit from 64KB
units, however you should take the RAID stripe size into account. Use the RAID stripe size recommended by the
manufacturer; however, make sure that the Windows allocation unit resides on the single RAID stripe. For example, a
1MB RAID stripe size works fine with 64KB windows allocation units hosting 16 allocation units per stripe when disk
partitions are aligned.
■
Tip
You can read more about partition alignments at:
http://technet.microsoft.com/en-us/library/dd758814.aspx
.
Finally, you need to analyze network throughput. Network performance depends on the slowest link in the
topology. This is especially important in cases of network-based storage when every physical I/O operation utilizes
the network. For example, if one of the network switches in the path between SQL Server and a SAN has 2-gigabit
uplink, the network throughput would be limited to 2 gigabits, even when all other network components in the
topology are faster than that. Moreover, always remember to factor in the distance information travels over a network.
Accessing remote data adds extra latency and slows down communications.
Operating System Configuration
You should look at the operating system configuration in the next step. It is especially important in the case of a 32-bit
OS where the amount of user memory available to processes is limited. It is crucial that you check that SQL Server can
use extended memory and that the “Use AWE Memory” setting is enabled.
■
the 32-bit version of SQl Server can use extended memory for the buffer pool only. this limits the amount
of memory that can be utilized by other components, such as plan cache and lock manager. it is always beneficial to
upgrade to a 64-bit version of SQl Server if possible.
Note
