Managing TempDB in SQL Server: TempDB Configuration

Managing TempDB in SQL Server: TempDB Configuration

Rate This
  • Comments 16

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows:

1.   Where should I create TempDB?

2.   What should be the size of TempDB?

3.   Single file vs multiple file?

Let us tackle each of these questions in Order.

Where (i.e. what disks) should I create TempDB?

More often than not, customer may not realize that the cause of the slowdown in the workload is because the TempDB is on the slower device. This can happen as workload changes over time or the TempDB was configured on a slower device. The performance of your TempDB is critical to your workload as, I had indicated in my earlier blogs, the SQL Server uses TempDB to store intermediate results as part of executing a query, for example to create a hash table or to sort as a result of order by. So what should you do? Well, it is not any different than what you will need to for your user databases. You need to measure the IO bandwidth needed to meet the demands of your workload. Since the persistence requirements of TempDB are different (i.e. no REDO needed), you may want to consider creating TempDB on its own spindles. In fact, you may even consider using RAM Disk to achieve better performance. Please refer to the KB article http://support.microsoft.com/kb/917047

What should be the size of TempDB?

Unlike user databases where you can probably estimate the growth rate of tables over time based on the workload, estimating the size of TempDB is non-trivial. The size of TempDB can be affected for example by a change in query plans, version store when there is a long running transaction and so on. The best way to estimate the size of TempDB is by running your workload in a test environment. Once you have a good estimate of the size of TempDB, use Alter Database command to set its size with a safety factor  that you feel is appropriate. Never, I mean never, let the TempDB grow to its steady state size through auto-grow. You should only use auto-grow as a last resort but not as a strategy. Also, remember that the TempDB is created every time you restart a SQL Server but its size is set to either default of Model database or the size you had set using Alter Database command (the recommended option)

·         Don’t rely on auto-grow to manage file sizes. You must set the appropriate size of TempDB using Alter Database command. Here are the pitfalls of auto-grow

o   Auto-grow causes a pause during processing when you can least afford it

o   Less of an issue with instant file initialization

o   Auto-grow leads to physical fragmentation

Should I use 1 file or multiple files for TempDB?

SQL Server recommends that you create as many files as there are COREs or CPUs to minimize allocation bottlenecks. Yes, it is true that starting with SQL Server 2005, there is better caching of TempDB objects but there are cases when the caching does not work and even with caching, the size of temp objects grows beyond 1 page (# tables) or 1 extent (internal objects), the SQL Server can potentially incur the allocation bottleneck. Please refer to the allocation-bottleneck for details on what causes allocation bottleneck.  Before we proceed further, you may wonder why we talk about allocation contention in the context of TempDB and why not in the context of other databases. Clearly, the allocation contention can happen even in user database. The main reason is that the objects in TempDB are created/destroyed all the time to process customer workload which leads to order of magnitude more allocation contention.

To minimize allocation bottleneck, here is the recommendation

·         Spread TempDB across atleast as many equal sized files as there are COREs or CPUs. Since allocation in SQL Server is done using proportional fill, the allocation will be evenly distributed and so is the access/manipulation of the allocation structures across all files. Note, you can always have more files than COREs but you may not see much improvement. One of the questions that people often ask is if they should create multiple files on the same physical spindle or multiple?  The allocation bottleneck can be reduced by creating multiple files on single or multiple spindles. Creating files across multiple spindles will help increase the IO bandwidth but will have no additional impact on allocation bottleneck.

·         If you are still encountering allocation bottleneck, you may want to consider enabling TF-1118. Under this trace flag SQL Server allocates full extents to each TempDB object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in TempDB. This trace flag has been available since SQL Server 2000. With improvements in TempDB object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. If your workload is not encountering SGAM contention, then enabling TF-1118 will not help. Also, this TF is instance wide and impacts the user databases as well.

Thanks

Sunil Agarwal

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • PingBack from http://www.codedstyle.com/managing-tempdb-in-sql-server-tempdb-configuration/

  • How many times in a day you observe the TEMPDB in your SQL Server enviornment? How many times in a week

  • Hi,

    Very useful article. Can you please provide some more explanation:

    1. "Never AutoGrow": Let assume I have a 4 CPUs Server, using SQL Server 2000. I split tempdb files (MDF + 3 extra Files) in equal file size. The current max tempdb size is 10 GB, I allocate 5 GB to each file so that their combined capacity is 20 GB, double of the current tempdb workload.

    If I set autogrowth option to zero to apply the "Never Autogrow" rule. What would happen if ever the SQL Server runs into a situation where tempdb will need more than 20 GB?

    What is the autogrowth value do you recommend?

    2. Can the templog.LDF be left at the default setting? (1 file, autogrowth 10%, unlimited max size)?

    Thanks you very much in advance for any advice.

  • When I said 'never autogrow', I meant don’t  let the TempDB grow to its steady state size through auto-grow. You should only use auto-grow as a last resort but not as a strategy. So you must enable auto-grow for emergencies but set your TempDB size appropriately for steady state

    For LDF file, similar to what we have said for data files, you will need to know what is the steady state size of the log file and then configure your TempDB to support that. Yes, just like data file, you should set  this to autogrow.

  • Hi,

    Thank you very much for your help.

    Understood for Q1.

    As for Q2. I am sorry I was not clear. I meant is there any need to split the tempdb LDF. (create one extra file for LDF per CPU, the same way than what you recommended for MDF).

    Thanks.

  • Creating multiple log files does not help allocation contention issue becauase the log is append only which is dfferent from regular data. If you log file does not fit on avaikabel space on the drive, you may create multiple files to meet the capacity

    thanks

  • Creating multiple log files does not help allocation contention issue becauase the log is append only which is dfferent from regular data. If you log file does not fit on avaikabel space on the drive, you may create multiple files to meet the capacity

    thanks

  • Sunil , can you provide a lab that can prove that having multiple tempdb files spread on one physical disk will improve performance (I mean one physical disk and multiple tempdb files).

    Regards

    Abhay

  • no lab but ut has been successfully used with many customers to minimize allocation contention

  • no lab but ut has been successfully used with many customers to minimize allocation contention

  • Hi

    So it is your recommendation that you should have say 4 files and 1 tempdb logfile if you have four procs? Or should I config with 1 tempdb file for each proc and 1 tempdb log file for each proc?

    Thank you in advance

    Tomas

  • You can only have one log file per database.

    Only data files can be added.

    Regards,

    Arif.

  • Hi,

    Very useful indeed. I have a question. I am configuring an SQL 2008 R2 server at the moment. It has 1 processor, 4 cores and 8 threads. I'm assuming that I would have 4 tempdb files and not 8?

    Thanks

    Paul

  • Run the sql command below to see how many visible processors are available on a given server. Generally you want to ensure you have as many tempdb data files as there are CPU's or vCPU's. You'll only need one log file. Make sure both data and log files are not set to the standard autogrowth...

    --Check for number of CPU's

    SELECT COUNT(*) AS processor_count

    FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

  • Nice article ...

Page 1 of 2 (16 items) 12