SharePoint and The Importance of TEMPDB

  • Article

 

One of the most important SQL Databases with regards to SharePoint is the TEMPDB.  Most SharePoint performance issues are due to improper SQL configuration and more importantly, not giving enough thought to the tempdb itself.  In SharePoint, every action that you take is staged in the tempdb before it is committed.  Consider the tempdb a working area for SharePoint operations.  Dumping 300 list items into a SharePoint list?? Guess where the operation hits first, yep, tempdb.

So lets get to some basic recommendations that could increase your SharePoint performance by 20 to 30% or more!

  1. First, move the tempdb to a separate LUN or set of high speed spindles.  I am not necessarily talking the LDF files, but more importantly the MDF files.  Move them off and keep them separate!
  2. Add multiple files to your TEMPDB “called NDF files”.  SQL is a multi-threaded application and can simultaneously write to more than one file at a time so why not add some NDF files to your tempdb. 
  3. Once you add multiple files, if at all possible, move those off to separate spindles too!
  4. Pre-Size your tempdb files this way SQL does not incur expansion operations during large usage of SharePoint

So how many files should you have for your tempdb?  There are some basic calculations but what I always do is divide the total number of processor cores by 4.  That is a good start!

image

If you have a quad core, quad processor system equating to 16 core processors, you would get a total of 4 TEMPDB files.

Some would argue that dividing by 2 is the right method; using that formula, the above example would give you 8 TEMPDB files.  My recommendation is to test with 4 first, then move up because like most tweaks, they will almost eventually taper off and probably provide negative performance. 

For those of you that do not think that performing these actions will realize performance, try it – you will be surprised.  Setup up two vm’s one with SQL and one with SharePoint, and load up 1000 list items into a SharePoint list and calculate how long it takes.  Try the recommendations above, then create a new list and load those same 1000 items.  Notice the improvement??

For those of you that would like to see the SQL script to add files + presize them – I have included it below!  In the example below, I am pre-sizing the files to 20GB.  AND as with all scripts, test them in a “dev” environment please!

Hope this helps!

ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'tempdev' , SIZE = 20480000KB )

ALTER DATABASE [tempdb]
ADD File ( NAME = N'tempdev_2', FILENAME = N'G:\SQL\TempDB2\tempDB2\tempdev_2.ndf' , SIZE = 20480000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

ALTER DATABASE [tempdb]
ADD File ( NAME = N'tempdev_3', FILENAME = N'G:\SQL\TempDB3\tempDB3\tempdev_3.ndf' , SIZE = 20480000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

ALTER DATABASE [tempdb]
ADD File ( NAME = N'tempdev_4', FILENAME = N'G:\SQL\TempDB4\tempDB4\tempdev_4.ndf' , SIZE = 20480000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

GO