TempDB - setting the initial size

09 January,2014 by Tom Collins

Question: what should be the initial size of TempB?

Answer: The short answer :  “depends on the usage” patterns. In general  a low usage or workload Tempdb will require a smaller TempDB . A high usage or workload TempDB will require a larger TempDB

 There is all types of activity occurring in TempDB.  Read more on the types of SQL Server Objects using TempDB  SQL Server - TempDB performance and strategy checklist

The interesting thing about the list of SQL Server Objects is the range of objects. Everything from database mail , UDF and Version Store. The usage of TempDB is set to grow as more features are added to SQL Server.

 As an added note, avoiding auto growth  will assist in TempDB performance. I’m not saying to completely avoid auto growth – as it can still act as a fail-safe when unexpected usage occurs. 


Indicators to assist in setting the TempDB initial size

a)     Work usage patterns -

b)    Largest index size - SQL Server - How to find the largest sql index and table size

c)     Types of queries  including CURSORS and the amount of data required in sorting List current SQL Server tempdb statements  

Once you’ve set the initial size, monitor and adjust accordingly.


Read More on Shrinking TempDB , Sort Warnings and SQL developers

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server - Shrink tempdb without sql server restart - SQL Server ...

SQL Server – SORT IN TEMPDB and Sort Warnings - SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on TempDB - setting the initial size

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer