SharePoint administrators often assume the role of itinerant (or occasional) SQL administrators because the performance of the data tier directly affects the performance of an entire SharePoint farm.
With this thought in mind, we approach the idea of planning and optimizing SQL for
SharePoint use. Although your organization might already have a designated SQL database
administrator, there is a good chance that this may be his or her first encounter with supporting the data tier of a SharePoint environment.
Knowing the behaviors, maintenance requirements, and performance characteristics of your content, configuration, and service application databases enables you to more clearly relate your desired strategy for long-term performance and growth goals to the SQL team.
Choosing a storage type
The type of storage configuration chosen for the SQL data tier will have a direct bearing on the performance of the completed SharePoint farm. In a server-based environment, storage is most often attached either directly to the server using Direct Attached Storage (DAS) or attached via a Storage Area Network (SAN).
In either type of storage implementation, the way in which these disks are organized and grouped together can have a direct bearing on performance.
Within a SharePoint farm, the design of the farm and which service applications or functions are to be implemented will determine what type of storage arrangement will be chosen.
There several factors to be considered:
- Is the service application or function more sensitive to read or write speeds? Is there a balance to be had between the two?
- On an Internet site on which people consume a lot of data, but does not change often, you may want to focus on read speeds for your storage.
- For a series of collaboration sites in which the data itself is changing on a regular basis, you might choose to balance read and write speeds.
- For a service application such as search or for the TempDB of a SQL instance, you may want to focus on write speeds for your storage.
- Is the storage mechanism chosen more cost-prohibitive than need be?
- A RAID 5 array (RAID configurations are discussed in the next section) containing five 100 gigabyte (GB) drives could store 400 GB of data (100 GB would be lost to maintain data parity). This array could withstand the failure of only a single drive; would have excellent read but poor write performance characteristics.
- A RAID 10 array would require eight 100 GB drives to contain the same amount of storage (400 GB for the data; 400GB mirrored). This array would theoretically be able to withstand the failure of four drives (but only one per mirror set), and would offer both excellent read and write performance characteristics.
RAID configuration levels
Redundant array of independent disks (RAID) is a technology that uses either hardware or software to group and organize hard drives into one or more volumes. The RAID level chosen can meet one of two possible objectives:
- Redundancy Allows for the grouping of drives to be able to withstand the failure of one or more individual drives within the group.
- Performance Altering the arrangement and configuration of drives within the group results in performance gains.
REAL WORLD AVOIDING SOFTWARE RAID IN A PRODUCTION FARM
Although you can choose to use software RAID, it is not recommended to do so in a production, on-premise system because the operating system of the server to which the storage is attached is responsible for maintaining the RAID configuration. This maintenance consumes both memory and processor resources on the host system.
There are four RAID levels commonly used within the storage subsystems of a SharePoint farm (particularly within the data tier): 0, 1, 5, and 10.
- RAID Level 0 “striping” distributes the reads and writes across multiple physical drives (or spindles).
- Performance This arrangement offers the absolute best performance for both reads and writes in your storage subsystem.
- Redundancy This arrangement has absolutely no tolerance for any individual drive failures; if a single drive fails, the entire array is destroyed.
- RAID Level 1 “mirroring” utilizes an identical pair of disks or drive sets to ensure redundancy.
- Performance This arrangement offers the same read performance as RAID Level 0 (assuming the same number of physical disks/spindles), but write speed is reduced as the number of input/output (I/O) write operations per disk is doubled.
- Redundancy This arrangement can withstand the failure of a single drive or drive set.
- RAID Level 5 “block level striping with distributed parity” distributes reads and writes across all drives, but also writes parity in a distributed fashion across all drives.
- Performance This arrangement offers the same read performance as RAID Level 0, but it incurs a fairly steep write penalty as the parity operation increases write overhead.
- Redundancy This arrangement can withstand the failure of a single drive within the drive set.
Performance prioritization
Within a SQL data tier, there are four distinct groupings of databases and files you should consider from a performance perspective. The assignment of these groupings to different storage types can have a dramatic effect on performance.
Although you could theoretically put all your databases on a RAID 10 disk set, doing so would be wasteful from a cost standpoint and somewhat ineffective. Conversely, assigning write-heavy databases to a RAID 5 disk set would result in a heavy performance penalty.
The four groupings to consider are (in terms of priority from highest to lowest):
■■ TempDB files and transaction logs
■■ If possible, assign these to RAID 10 storage
■■ Allocate dedicated disks for TempDB
■■ Number of TempDB files should be equal to the number of processor cores (hyperthreaded processors should be counted as one core)
- All TempDB files should be the same size
- An average write operation should require no more than 20 ms
- Database transaction log files
- Should be on a separate volume from the data files
- If possible, assign them to RAID 10 storage
- Write-intensive
- Search databases
- If possible, assign these to RAID 10 storage
- Write-intensive
- Database data files
- Can be assigned to RAID 5 storage with the understanding that writes may be slower (for better performance, consider using RAID 10 storage)
- Read-intensive, especially useful for Internet-facing sites
Pregrowing content databases and logs
Pregrowth is the act of preemptively growing a content database (or its associated log file) to a designated initial size. This size can be an estimate of how big you might expect the database or log to grow because the database administrator (DBA) can also shrink the files somewhat if not all the space is eventually used.
Pregrowing a database has two benefits:
- A reduction in the amount of I/O because a database has to be expanded every time data is added
- A reduction in data disk fragmentation
REAL WORLD USING THE MODEL DATABASE WITH SHAREPOINT
The SQL model database (part of the system databases) can be used to control the initial size of newly created content databases. If you choose to do so, just remember that any service application, configuration, or other databases will all echo the model database’s initial size. Often, SharePoint administrators will configure the entire farm first (Central Administration/Configuration/Service Applications) and then alter the model database prior to creating content databases.
Pregrowing the database is done from within SSMS; there is no way to configure initial size from within Central Administration.
To alter the size of a database or its associated log, do the following:
1. Open SSMS.
2. Locate your content database and right-click it; select Properties.
3. In the Select A Page Panel, select Files.
4. In the Database Files section (right panel), click within the Initial Size (MB) cell for your database or transaction log and change this value (see Figure 1).
FIGURE 1 Database properties (initial size).
5. Click OK.
Configuring content database autogrowth
Autogrowth is the amount at which a database or its log grows after it reaches its current size limit. When a SharePoint content database is created from within Central Administration, its default autogrowth rate is set to 1 megabyte (MB).
As an example, if you had a database that was at its current size limit and you added a 10 MB file, the database file could be grown a total of 10 times before it could store the file. Imagine how much I/O this could generate multiplied over 1,000 files.
The SQL model database (part of the system databases) cannot be used to control the autogrowth rate of newly created content databases. After its initial creation, consider modifying the rate at which a content database file grows after it is full.
To alter the size of a database or its associated log, follow these steps:
1. Open SSMS.
2. Locate your content database and right-click it; select Properties.
3. In the Select A Page panel, select Files.
4. In the Database files section (right panel), click the ellipsis box within the Autogrowth/Maxsize cell for your database or transaction log and change this value (see Figure 2).
FIGURE 2 Autogrowth properties (starting value).
5. When the Change Autogrowth dialog box appears, you can select to configure file growth in percent or in megabytes (see Figure 3).
FIGURE 3 Changing autogrowth.
IMPORTANT DO NOT LIMIT THE MAXIMUM FILE SIZE OF A SHAREPOINT DATABASE
It is rarely a good idea to limit the maximum file size of a SharePoint content database or its associated transaction log. Doing so can have unintended results and appear as an error to your users if the size limit is reached.
6. The new value for autogrowth is shown in the Database Properties dialog box for your content database (see Figure 4).
FIGURE 4 Autogrowth properties (final value).
7. Click OK.
One final note: Choosing to either adjust autogrowth rates or pregrow a database can trigger the SharePoint Health Analyzer rule called Database Has Large Amounts Of Unused Space. This message can be safely ignored because you intend to eventually fill the available space with data.
Specifically, this message will appear if the unused space is more than 20 percent of the disk space, and the unused space is greater than the autogrowth size plus 50 MB.
advanced content database performance
As content databases grow in size, their overall size can cause performance degradation. Depending on the content present in the database, separating the database into multiple smaller content databases may be impractical.
One of the potential solutions to this issue is to split a larger content database file into multiple smaller files that are still part of the same database. If you decide to go with this approach, spreading these files across separate physical disks results in far better I/O.
As with the TempDB database, the number of data files for any “split” content database should be less than or equal to the number of processor cores present on the database server. If hyper-threaded processors are used, each should be counted as a single core.
“Choosing to split content databases across multiple database files has a side effect where SharePoint administration is concerned: SharePoint Central Administration cannot be used to back up and restore a split content database. After they are split, the databases must be backed up and restored from SQL server because SharePoint specifically does not understand how to restore multiple files to the same content database”
