Improving MSSQL performance – the forgotten TEMPDB database

Facebooktwitterpinterestlinkedinmail
Applies to all versions; Author: Paweł Jawień

The performance of WEBCON BPS is closely related to the performance of MS SQL on which the BPS database operates.

Once we’ve overcome RAM shortages, optimized storage space for the process database, relieved overburdened processors and implemented a suitable database maintenance schedule, we may think that we’ve got our backs covered, only to then realize that we forgot to optimize the TEMPDB database (even though the developer suggests starting with that).

Let us take a look at some official recommendations from Microsoft for optimizing SharePoint performance.

TEMPDB is one of the important systems DB regarding the health of the SharePoint.  In SharePoint almost every action/request is generating work in the TEMPDB.

Recommendation for TEMPDB performance:

  • Create the TEMPDB database on the fastest storage available (SSD is a great option that would benefit in many ways). (In cluster 2012 TEMPDB can be on local disk resource (local SSD) as long as it exists on all nodes); always separate the user DB files from the TempDB files.
  • Pre-allocate space for your TEMPDB files by setting the Initial File size to a larger value so it can accommodate the typical workload in your environment, you can go by a rule of thumb that the size should be 25% of the largest content DB.
  • Create multiple data files to maximize disk bandwidth and reduces TEMPDB file contention, Make each files the same size; this allows optimal proportional-fill performance.
  • Allow your TEMPDB files to grow automatically and monitor the disk free space, set the file growth in fix size and not in percentage.

In order to prove that these recommendations do indeed work, we set up a small test. On a “blank” MS SharePoint 2013 we added 3000 new lines to a SharePoint list.

During our first attempt, the TEMPDB database was located on the system disk, (like 70% of all our installations). For our second attempt, we placed TEMPDB on a separate SSD (really fast “server” disk).

The time it took to carry out the test operation was 44 times shorter on the SSD than it was on the standard drive (15,000m RPM) that was shared with the operating system. Obviously these are fringe cases, but the difference is impressive nonetheless, and definitely worth the effort.

When dealing with real-life circumstances and realistic budgets, we have to be able to reach a compromise. It’s also worth taking a look at how MS recommends we distribute the databases used by SharePoint:

For collaboration or update-intensive sites, use the following ranking for storage distribution.

The highest ranked item should be in the fastest drives.

  1. tempdb data files and transaction logs
  2. Content database transaction log files
  3. Search databases, except for the Search administration database
  4. Content database data files

In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows.

The highest ranked item should be in the fastest drives.

  1. tempdb data files and transaction logs
  2. Content database data files
  3. Search databases, except for the Search administration database
  4. Content database transaction log files

As you can see, regardless of how you choose to use you SharePoint server, the TEMPDB database should always be placed on the fastest disk when possible.

There is also one more suggestion to make regarding the TEMPDB database. It is recommended that you create multiple files for TEMPBD database (depending on the number of processor cores).

There are a number of theories which claim there should be as many files as there are cores, or half as many files as cores, or a quarter as many files as cores.

If we do not have the required number of hard drives, for each file of the TEMPDB database to have its own drive, we recommend that you do not bother with dividing the database into multiple files and just stick to one TEMPDB database file – but place it onto your fastest hard drive.

Leave a Reply

Your email address will not be published. Required fields are marked *