Accidental DBA TempDB Tips
TempDB optimization is important for every SQL Server installation, regardless of version. Often an accidental DBA will use the default TempDB installation option, and many times will experience adequate performance. However, there are a few techniques you can use to optimize your TempDB. Since this database is used by SQL Server across all databases, it can be one of the easiest optimizations you can make.
Here are some suggestions I have garnered from many different sources…they are not new to me, but not worthy of annotation at this point.
- Create separate data files for each CPU/Core. Make the files the same size, even if they are on the same drive.
- Your system may work better with a different TempDB file count. Monitor different configurations for performance.
- Place TempDB files on the fastest drive available (beware of SSD…great performance, but might burn it out).
- If possible, isolate TempDB on a separate disk from other databases.
- You only need one TempDB log file, but it should also be on the fastest drive available
- Make sure you have enough space in your TempDB files so they do not have to autogrow.
TempDB has some optimization techniques. It caches space for tables that may be used again. For this reason you may see the data allocation of TempDB remain long after your query has completed.
Be sure to commit or roll back your transactions. If you don’t, any space allocated in TempDB as part of that transaction may not be released.
Remember, if you decide to change the files and/or the size of your TempDB files you will need to stop and restart SQL Server in order for your changes to take place.
If this is new to you, I encourage you to find some thorough documentation on TempDB and how it supports your SQL Server installation. It is a cornerstone for SQL Server optimization, and since some optimization is rather easy, it shouldn’t be ignored.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Total Database Information At Your Fingertips (Part – III)
This article is good for the novice who has recently started their career in databases and has had to scratch their head sometimes at some silly results or code. Experienced database developers or administrators are probably familiar with this, but it’s still good to take a look over these things as it helps your memory. This article contains something that is not needed frequently but when needed it become backbreaking to get the results if we don’t know which queries to execute.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)
Featured Script
fn_ipAddressProperties
T-SQL IP-address details function. Given an ip and netmask the funtion will display characteristics such as hostmin, hostmax… (read more)