TempDB must be optimized after some time interval for every SQL Server. There are some techniques to optimize TempDB. Here are some suggestions and techniques found from different sources:
Place TempDB files on the fastest drive available (beware of SSD…great performance, but might burn it out).
Your system may work better with a different TempDB file count. Monitor different configurations for performance.
Create separate data files for each CPU/Core. Make the files the same size, even if they are on the same drive.
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.
Place TempDB files on the fastest drive available (beware of SSD…great performance, but might burn it out).
Your system may work better with a different TempDB file count. Monitor different configurations for performance.
Create separate data files for each CPU/Core. Make the files the same size, even if they are on the same drive.
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.
No comments:
Post a Comment