Today’s post is is build from my 10 minutes conversation with my client while working on Comprehensive Database Performance Health Check. The client had an amazing hardware configuration but their performance was extremely bad. After carefully investigating we figured out that his TempDB was not properly configured and had issues which were creating the major performance issues. We fixed quite a lots of things at customer place with regards to TempDB and he finally was able to get “the Flash” fast performance with his queries and application.
Here are the top 5 tips which we implemented at my client’s place to overcome all the troubles which they were having with TempDB.
1) Pre-Size TempDB
There is no absolutely science how big your TempDB should be. However, here is what I tell my customer, size it at least as big as your biggest Index is so when it is rebuilt your TempDB does not have to grow bigger. If you are not sure about it, just go ahead and keep your Temp database as 25% of the largest database, I think it is a good beginning spot.
2) Faster Drives – SSD
It goes without saying that you should have your Temp database on the fastest possible drive. I have often seen people keeping it on the slower drive or on the same drive as where they have installed the Operating System. This is indeed not a great idea. My personal preference is that you keep your Temp data and log files on the SSD drives.
3) Separate Data and Log Files for TempDB
This is always true for any of the databases including your Temp Database. Take advantage of IO Parallel operations by keeping your data and log to separate drive.
4) Multiple Data and Single Log Files
A very popular question is how many Temp data files should one have it. Here is the simple answer to it. As many as logical CPUs you have but not more than 8 in any case. If you have 4 logical CPUs you should have 4 Temp data files but if you have 12 logical CPUs you should cap your temp data files at 8.
5) Set FileGrowth to a Large Fixed Value
A common practice is to not set FileGrowth value for your Temp Database. I often see users setting this one on their user database but not the system database. For TempDB it is critical that you set a large fixed value for the autogrowth to avoid extra overhead on the CPU to grow every time your Temporary database is filled up.
There are few more tricks but these steps are the starting point to set your TempDB for the success. If you have any other suggestion, please do post that in the comments area so everyone who is reading this blog post can learn from it. Here is the link to official documentation from Microsoft.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I was under the impression that you should try and get the datafile size correct and not let it expand, especially with multiple files as they could be different sizes.
That is also a good thought process and nothing wrong with that sir.
Hello Mr. Dave. I would like to consult you to get one of my database fixed in which I am facing a lot of performance issues. Kindly let me know your contact details so that we can discuss this.
pinal at sqlauthority.com
Hi Pinal,
I have a database with RCSI on, on which there are merge operations that changes every row of the table one at a time.
This is a one time activity and during this time TEMPDB grows to about 800 GB and I see a lot of PAGELATCH_SH and PAGELATCH_EX. Do you think adding more TEMPDB files help with speeding up the process? Currently we have 8 tempdb datafiles and we surely have enough logical processors.
Secondly I came across this article which says to disable AUTO CREATE STATISTICS and AUTO UPDATE STATISTICS to optimize tempdb performance. What do you think about it?
When you say “Multiple Data and Single Log Files”, does it mean the data files can be in one drive? Or the data files should be in separate drives?
Will it be very bad if we keep the data files in one drive?
Thanks