Fine-Tune Your SQL Server 2012 Configuration for SharePoint 2013

As I discuss in "Configure SQL Server 2012 for SharePoint 2013," if you're using SQL Server 2012 as the database server for SharePoint 2013, you must configure SQL Server specifically to host SharePoint 2013.

Did You Know? Get training on April 21: "SharePoint 2013 and SQL Server 2012 Rock-Solid Designs with Colin Spence"

In this article I will continue the discussion by explaining some optional configuration tweaks that I like to make to my SQL Server 2012 instances for optimal performance.

Set Maximum Amount of SQL Server RAM

One optional tweak I like to make to my SQL Server 2012 configuration for SharePoint 2013 is to set the maximum amount of RAM that the SQL Server database engine can use. SQL Server is usually pretty good at sharing RAM and managing memory in general.

Related: Set Up SQL Server 2012 as a SharePoint 2013 Database Server (Part 1)

If you've ever looked at the memory usage on a SQL Server box, you'll see that SQL Server is using almost all the memory on the box. This is really a good thing. You don't want any unused RAM lying around, not if SQL Server can use it to cache things and give better performance. SQL Server is a good neighbor, though, and if other programs want some memory, SQL Server will free up some memory for them—most of the time.

Related: Configure SQL Server 2012 for SharePoint 2013 (Part 2)

Once in a while, SQL Server doesn't release RAM as it's supposed to. This happens pretty infrequently, but when it does happen, it's a huge pain. This usually means the server is running very slowly and remote tools can't get in to fix anything. To keep this from happening, I usually set the maximum amount of RAM that SQL Server can use, as shown in Figure 1.

Figure 1: Setting the Maximum Amount of RAM That SQL Server Can Use

There are probably better guidelines available, but on a typical server today that has 32GB or 64GB of RAM, I usually make the maximum amount of RAM around 90 percent of the system RAM. That leaves 10 percent or so, which should be enough for the operating system, backup software, and other essentials. If you have other SQL Server instances or SQL Server components (such as SQL Server Reporting Services) installed on the same server, make sure to adjust your headroom accordingly. The server in the video accompanying this article has 16GB, so I gave SQL Server roughly 12GB.

This setting is very intuitively on the Memory page of the instance settings. The important part is that I leave enough for the OS to let me in via Remote Desktop Protocol (RDP) if I need to access SQL Server remotely to address the problem.

Opt for Compressed Backups

Compressing backups is another optional configuration setting I enable when configuring a new SQL Server instance. When you enable backup compression, your database backups will be compressed when native SQL Server backups are executed. An additional benefit is that the backups will also be compressed if you do a farm-level backup in SharePoint. Backups on your SQL Server system probably happen at times when the workload is lighter—when there are CPU cycles sitting around looking for something to do. Using those spare CPU cycles to compress your backups will make them take up less space and get the backup done faster, too.

How do you make all this magic happen? Open the properties of the SQL Server instance and navigate to the Database Settings page, shown in Figure 2.

Figure 2: Enabling Compressed Backups

Select the Compress backup check box. Doing so sets the default for the instance. Note that when you back up a database, you can still manually specify whether or not the backup will be compressed. Also notice that the page shown in Figure 2 is where you set the default locations for databases, log files, and database backups. We set these defaults during the install, but if you want to change them, this is where you'd do it. Changing a default setting only impacts databases that are created after you change the setting. It won't magically move your existing databases for you.

Set the model Database's Recovery Model

There's one final optional tweak I make before I turn SQL Server over to SharePoint, especially if it isn't a production environment. I set the model database's recovery model to Simple. This setting tells SQL Server to overwrite transactions in the transaction logs that have been committed to the database files. This keeps those sneaky .ldf files from filling up your drive and taking down SQL Server. No one has time for that.

Just as you can set the default behavior for backup compression, you can set SQL Server's default recovery model behavior. SQL Server has a database called model that it uses if certain settings aren't defined when a database is created. The Recovery model setting is one of those settings. If a database doesn't have the Recovery model setting defined when it's created, then that database will get whatever options the model database has. Of course you can change the options for your database later if you want to. You will not want the Simple recovery model if you start using some high-availability functionality such as mirroring or AlwaysOn Availability Groups. If you decide to use any of those features later, you'll have to switch the Recovery Model setting back to Full.

To set the recovery model, find the model database under System Databases in Object Explorer. Right-click the model database and open its properties. On the options page, shown in Figure 3, you'll find the Recovery model setting options. Select Simple from the drop-down list and click OK.

Figure 3: Setting the model Database's Recovery Model to Simple

Again, you can change this setting at any time both on individual databases and on the model database itself. Not all SharePoint's databases inherit their recovery model setting from model, so you'll need to keep a watchful eye on those .ldf files, even if you set the model database recovery model to Simple.

Following the setup and configuration guidance I've provided in this article series will help to ensure that your SQL Server 2012 instance works smoothly and reliably for your SharePoint 2013 farm. Check out the list at the end of this article for links to the other articles in the series, plus additional helpful resources.

Other Articles in This Series

Additional SQL Server-SharePoint Resources