Allocated Space for TempDB | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Allocated Space for TempDB

Hi everyone, it’s been a while <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />We have a few servers with 3gig – 8gig temporary databases however only 100 megs or so of the file is used by data. On the other hand, we have newer servers that have tempdb’s that are no larger than 125megs total! This drew a red flag right away but I think I have figured it out. We must have had a stored procedure that hit the tempdb pretty hard for a while until our development team optimized it. After that we added some new database servers which were not victims of the bad code, thus their tempdb’s stayed at a manageable size (125megs or less).<br /><br />Now here’s my question. I know the tempdb is recreated when SQL Server is restarted, but does it create a new tempdb based on the size of the previous tempdb?? For example, if the tempdb had 11gigs of allocated space before restarting SQL Server, would it automatically allocate 11gigs when it gets recreated?? I think this is the case, just want to confirm with you guys. <br /><br /><br />Who knows, maybe others out there have alot of allocated space for their tempdb’s that isn’t necessary.<br /><br />Thanks in advance
quote:For example, if the tempdb had 11gigs of allocated space before restarting SQL Server, would it automatically allocate 11gigs when it gets recreated?? I think this is the case, just want to confirm with you guys.
I don’t think so. After restart tempdb will take the space defined as initial file size for each tempdb file. From BOL:
quote:Unlike other databases, however, it is reset to its initial size each time the database engine is started.
quote:Optimizing tempdb Performance
General recommendations for the physical placement and database options set for the tempdb database include: Allow the tempdb database to automatically expand as needed. This ensures that queries that generate larger than expected intermediate result sets stored in the tempdb database are not terminated before execution is complete.
Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.
Set the file growth increment percentage to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.
Place the tempdb database on a fast I/O subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use filegroups to place the tempdb database on disks different from those used by user databases.

]]>