Tip # 8 – Default TempDB Settings
Top 10 Tips for SQL Server Performance and Resiliency
This article is part 8 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is not all inclusive.
Most common mistake #8: Default TempDB Settings
By default when you install SQL Server the TempDB database is not optimized. For SQL Servers that use the TempDB even moderately, I recommend optimizing it for the best performance. The TempDB is the storage area that SQL Server uses to store data for a short periods of time, information that can be found in the TempDB include temporary tables, and data that is being sorted for query results. The data in the TempDB is so temporary that each time the SQL Server service restarts, the TempDB is recreated.
Due to the specific nature of the data in TempDB and the frequency of use it is important to ensure you have the most optimal configuration.
Optimizing the TempDB is so important that Microsoft is changing how it is installed with SQL Server 2016 to encourage the optimization of Temp DB. How do you optimize TempDB? It’s not difficult at all if you follow these few pointers:
Place your Temp DB on that fastest storage you have. Ultimately this storage should be pounding out a latency less than 5 milliseconds. Does your server have access to SSD storage? If so that is a great place for the TempDB.
There are a great number of studies that have been done to determine the ideal number of files you should split your Temp DB over. With my experience I tend to create one temp DB file for each processor core on the server, however I don’t do this until I find there is some contention in the TempDB.
Grow your TempDB to the size you need it. Your TempDB is going to be recreated each time your service is restarted, so if your default database size is smaller than the normal operational size you are going to have to go through some grow events. Speaking of growth events, it is better to have controlled growth rather than a number of little growth events, so we recommend reviewing the auto growth size.
If you have questions about Optimizing your SQL Server TempDB or need assistance with SQL Server in general, reach out to us! XTIVIA and I can assist you with adding resiliency for your business. Please don’t miss my other blogs regarding this topic. http://www.xtivia.com/contact-us