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)
