Databases Reference
In-Depth Information
physical spindles.'' That's easy to follow when you have a small disk array with 12 disks, for example. In
that scenario 3 x RAID10 volumes of 4 disks each would be a safe option.
It gets more difficult when you have a large SAN. More than likely if you have a SAN you have somebody
(or a team) that looks after it and presents the logical unit numbers (LUNs) of a size you specify to your
SQL Server, which you can then use. (An LUN is how you refer to a volume on a disk array.) The issue is
that they can't guarantee that your LUNs will be on separate spindles because they carve out your LUN
from free space on the array and they don't know how many or which spindles are yours. The idea is
that the arrays are monitored at the disk level to look for ''hot disks'' that are overworked so the data
can then be moved and spread around disks that aren't so busy. So even if you could work out which
spindles were yours, there's no guarantee they'll stay yours.
The question that then follows from that is, ''Are there any benefits to following the traditional recom-
mendations if you can't get separate spindles?'' The answer is yes, but the benefits are mainly from a
manageability perspective rather than performance:
If you keep your databases on separate LUNs, you can manage how much disk space each one
can grow into without affecting the others.
❑
If you move TempDB to a separate LUN, you can specify the size to be as big as the drive with-
out losing performance, so you'll never have to worry about growing TempDB.
❑
I know of at least one of the SQL Servers that Microsoft IT runs for an internal application that has a
200 GB TempDB on a 200 GB LUN with autogrow disabled. It's more than they'll ever need but TempDB
space will never be a concern and the size doesn't impact performance.
Another question I've been asked in the past is whether or not there is a performance benefit from a
Windows perspective by having multiple drive letters to multiple LUNs rather than 1 drive letter to
1 LUN. I could find no evidence theoretical or practical that would suggest that Windows benefits from
having multiple drive letters.
Best Practice
If you can put your data, transaction log and TempDB files on separate physical
spindles it will improve performance. If you can't guarantee separate spindles then
separate the files across partitions for manageability purposes.
Provisioning a Partition
After installing the disks, you need to configure them and the first step with a new disk is to
consider the partitions you wish to create. There are two choices of partition style currently available:
the classic MBR (Master Boot Record) and the new GPT (GUID Partition Table). Eventually GPT will
take over as the default choice as MBR only supports partitions up to 2 TB. However, there are very
few scenarios today that require this much space in a single partition and support for GPT has only
just become available in Windows Server 2003 SP1, so it's still relatively new and not yet supported for
shared disks in Windows Failover Clustering. MBR remains the recommended choice for all but the most
unusual of requirements.
Search WWH ::
Custom Search