TempDb is one of the critical databases of Microsoft SQL Server. It is used for many purposes
Temporary user objects
- Table variables
- Temporary tables
- Temporary procedures
- Global temporary tables
Intermediate results, work files and work tables
- 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.
At the beginning of my system, we set up the environment as below
Operation system: Window Server Standard 2012
CPU: 16 logical CPU cores
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.
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.
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.