Size of TempDB

Posted on 2014-08-13
Last Modified: 2014-08-29
What should I set for the size of TemDB database and log file ? Will it be too small for default size of 8MB ? How to determine the size on SQL 2008 R2 server ?

Question by:AXISHK
    LVL 15

    Expert Comment

    tempdb is mainly used for the following :

    Temporary user objects like #temp table and @table variables
    Internal objects  like work tables,intermediate results, MARS, work file, sorting  etc
    Version store, snapshot isolation level and change tracking
    Service Broker, database mail.
    DBCC CHECKDB, Online Indexing etc.

    8 MB is too small, however there is no good number for this but there should be enough size to avoid constant tempdb expansion, the initial tempdb size depends on your workload and whether you are using any of the above, Start with an average size then monitor the file size growth for a week or two then get then do some math to get the good number and resize the tempd so that tempdb growth is not triggered frequently.

    There is no issues of being  tempdb too large until you are running out of free space either in tempdb or the drive, but make sure that instant file initialization is on for the sql server service account, this helps to reduce the file expansion time in the event of auto-growth. Another import thing is the number of files of tempb general recommendation is to have .5 to 1 files per cpu cores to reduce tempdb contention and disk band width, make the size of the files equal to get the benefit of proportional fill.
    LVL 44

    Accepted Solution

    There's no a standard size for tempdb but I can let you some advices based in my experience:
    1) - tempdb datafiles and logfile should be in a separate disk
    2) - create at least 1 datafile per server core. If your server have more than 8 cores, you can stop at 8 datafiles and check the performance and if it's needed add 2 datafiles at a time and keep checking the performance until adding more datafiles doesn't boost more the performance.
    3) - all datafiles should have the same size so will help SQL Server engine algorithm for balancing the data
    4) - because tempdb it's used for some table operations, it's minimum size should be at least equal to the size of the largest table in the SQL Server instance.

    So, I think the point 4) can help you to define the size of the tempdb but I wanted to let you some more advices.

    Hope this can help you.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video discusses moving either the default database or any database to a new volume.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now