Properly size SQL Server TempDB for ConfigMgr

At MMS 2017, Benjamin Reynolds and I covered properly sizing TempDB as one facet in our Optimizing and Tuning SQL Server for ConfigMgr session. Because of a few conversations that occurred that week at MMS, I think it is appropriate to cover this here in a bit more detail.

As background, SQL Server TempDB is used for temporary objects created by queries, sort operations and more… from Microsoft docs:

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

First, the primary talking points:

  • Total TempDB should approximate 25-30% of the SCCM total size.
  • TempDB data files should be equally sized
  • Place TempDB on a dedicated drive, with log file
  • Create 4 (or more) equally sized data files. This helps you avoid the GAM/SGAM/PFS page contention issues described in Microsoft KB 2154845.
  • Turn off auto-growth
  • Create no more than 8 data files

Now, more detail on each of these points:

Total TempDB should approximate 25-30% of the SCCM total size.

As a starting point, calculate the total estimated size of the SCCM database. If you are not sure what that size will be, use Anthony Clendenen’s sizing calculator. Once you know the SCCM size, the total TempDB size can be calculated.

TempDB data files should be equally sized

This is often overlooked, once you have determined the total TempDB size, divide that by the number of data files (start with 4) and create EQUAL size data files. This will allow SQL Server to automatically choose the next available data file for operations.

Place TempDB on a dedicated drive, with log file

TempDB can be very IO intensive, if you have the ability to choose SSD storage for TempDB, do it. There is no benefit to separate the TempDB log file to another drive, place all TempDB data and log file(s) on a single, logical drive.

Create 4 (or more) equally sized data files. This helps you avoid the GAM/SGAM/PFS page contention issues described in Microsoft KB 2154845.

Page contention can occur on TempDB. By creating multiple data files, you can reduce contention as SQL Server will automatically roll to another TempDB data file if one is in use. Hence, performance can be increased by reducing contention.

Turn off auto-growth

If you have a logical drive dedicated for TempDB data and log file(s), consider filling the drive with the TempDB data files and turning off file Autogrowth.

Create no more than 8 data files

In general, never create more than 8 data files for TempDB. For SCCM, 4 equally sized data files seems to work quite well.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Properly size SQL Server TempDB for ConfigMgr

  1. Dustin says:

    Do you disable AutoGrowth on the DB files only or on the log files as well?

    • ConfigMgrMVP says:

      On TempDB – disable auto-growth on data and log file(s), if you have filled the drive. If you are not filling the drive, you can leave auto-growth enabled, use 512 or 1024MB growth size.

  2. Pingback: Properly configure SQL Server ConfigMgr database | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s