SQL Disk Prioritization For SharePoint

There are 23 default databases created when you install a complete SharePoint 2013 server. Some are predominantly write intensive and some read intensive. Some are a blend of both. The priority of disks/drives needs to be mapped to the priority of databases. In SharePoint, within a SQL data tier, there are four distinct groupings of databases and files we should consider from a performance perspective. The assignment of these groupings to various storage types can have a dramatic effect on performance. Ideally, the administrator should place the TempDB database, content databases, usage database, search databases and SQL Server 2012 transaction logs on separate physical hard disks. Data files can be distributed across disks.

Although you could theoretically put all of 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. When the administrator prioritizes data among faster disks, the following ranking can be used.

1. Tempdb data files and transaction logs

The ranking for Tempdb data files and transaction logs are:

• If possible, assign these to RAID 10 storage
• Allocate dedicated disks for TempDB
• The number of TempDB files should be equal to the number of processor cores (Hyper-Threaded processors should be counted as one core)
• All TempDB files should be the same size
• An average write operation should require no more than 20ms

2. Database transaction log files

The ranking for Database transaction log files are:

• Should be on a separate volume from the data files
• If possible, assign these to RAID 10 storage since these are write intensive

3. Search databases, except for the Search administration database

Search can generally generate anywhere between 3500 and 7000 I/O per second depending on how search is used, how many content sources are configured, how many custom extractors are used and so on.

• If possible, assign these to RAID 10 storage since these are write intensive

4. Database data files (Content Data and other DB files)

• Can be assigned to RAID 5 storage with the understanding that writes maybe slower
• Read intensive, especially useful for Internet-facing sites

The Data, Log and Backup File Paths need to be carefully chosen and as a best practice (from data recovery perspective) should be kept on separate drives. So in case you lose the data file you do not also lose the transaction and log files.