TempDb is one of the critical databases of Microsoft SQL Server. It is used for many purposes

Temporary user objects

  • Table variables
  • Temporary tables
  • Cursor
  • Temporary procedures
  • Global temporary tables

Intermediate results, work files and work tables 

  • Sort
    • Rebuild Index
    • Group By, Order By
  • Hash Join and Hash Aggregates
  • Spills to tempdb
  • Temporary LOB storage
  • Returned tables for table valued functions
  • Common Table Expressions (CTEs)

Version store for data modification

  • Read committed snapshot isolation
  • Online indexes
  • Snapshot isolation

There are a lot of operations which Microsoft needs to use tempdb database. It can cause tempdb contention which is the most critical issue that affects performance of any active system. So how to set up approximated tempdb is one of the most important task for any database administrator. In this article, I would like to share the tempdb contention in my system and how I resolved it.

Problem

At the beginning of my system, we set up the environment as below

Operation system: Window Server Standard 2012

CPU: 16 logical CPU cores

RAM: 64GB

SQL Server 2014 Standard

The system run very smoothly for 2 years with a small data. However, the data has been growing up quickly from 2017 and the performance of the system was very terrible. After analyzing and reviewing the system, I recognized the tempdb contention and tempdb had only one data file and one log file.

Solution

As Microsoft recommended under  KB article 2154845, I did the following steps for tempdb configuration

  • Created 4 data files because my system has 16 logical CPU cores so I create 4 data files
  • Monitored tempdb contention
  • After one week, tempdb contention still happened
  • Increased number of data files by 4, the system had 8 data files for tempdb
  • Continued monitoring tempdb contention

Fortunately, tempdb contention disappeared after I increased number of data files to 8.

Conclusion

Actually, if you read the recommendation from Microsoft I mentioned above, you will see that they just recommended data file per CPU core ratio. However, there are several factors that can still affect tempdb performance in my view. I would like to summarize as the best practices when you have the same issue (tempdb contention). Obviously, you should still try to monitor your system after changing the configuration because the systems are very different. Sometimes, the change works perfect on my system but they can not work on your system.

DATA FILE PER CPU CORE RATIO

1. Start With 1 data file for every 2 or 4 logical CPU cores, up to a maximum of 8
2. Monitor for tempdbcontention
3. If tempdbcontention is detected, increase the number of data files by 4
4. Repeat this process until contention is resolved, up to a maximum of 1 data file per logical CPU core

PRE-SIZE FILES TO AVOID AUTO-GROWTH

The reason I list out the factor when you have more than one data file for tempdb database, Microsoft will auto-growth the largest file if data files are not the same size. This can still cause tempdb contention even though you have multiple data files for tempdb. I recommend you should pre-size the data files and turn off auto-growth option so that they are all the same size and size enough that SQL Server won’t need to grow the data files. The common formula I would like to recommend here.

  •  Data file = (Size of drive * 90%) / (Number of data files + 2)
    [log file will be double the data file])
  • Log file = Data file * 2

For example, you have a 200GB drive to use for tempdb, you create 8 data files for 16 CPU cores

  • Data file = (200 * 90%) / (8+2) = 18
  • Log file = 18 * 2 = 36

It means that you create 18GB for each data file and 36GB for the log file.

DISK PERFORMANCE AND RAM

Obviously, everything is on RAM that we all expect but nothing is perfect so we should try to put tempdb on the fastest disks possible and having a plenty of RAM.

 

 

 

 

Advertisements

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