Share via

Managing TempDB in SQL Server: TempDB Configuration

  • Article

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 https://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

Comments

  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.

  • Anonymous
    December 01, 2009
    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.

  • Anonymous
    December 01, 2009
    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.

  • Anonymous
    December 01, 2009
    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

  • Anonymous
    December 01, 2009
    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

  • Anonymous
    October 08, 2010
    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

  • Anonymous
    October 12, 2010
    no lab but ut has been successfully used with many customers to minimize allocation contention

  • Anonymous
    October 12, 2010
    no lab but ut has been successfully used with many customers to minimize allocation contention

  • Anonymous
    November 10, 2010
    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

  • Anonymous
    April 01, 2011
    You can only have one log file per database. Only data files can be added. Regards, Arif.

  • Anonymous
    August 11, 2011
    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

  • Anonymous
    May 20, 2012
    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'

  • Anonymous
    July 19, 2012
    Nice article ...

  • Anonymous
    September 20, 2012
    Hi Sunil, I have one doubt, the TEMPDB database should have the same number of files that CPUs that SQL Server uses, if SQL Server uses 32 processors should I have to create 32 tempdb files? I was reading this: msdn.microsoft.com/.../ms175527(v=sql.105).aspx And there they said that you should create multiple files for better performance, but not to many files but they don't say which is the limit. Do you know anything about it?

  • Anonymous
    May 13, 2015
    This article is nice - very simplistic, down to the basics, and not a lot of complicated wording. I came here after visiting cc.davelozinski.com/.../increase-sql-server-tempdb-performance and like how you keep your articles easy enough to read for us non DBA types. :-)

  • Anonymous
    June 29, 2015
    "Spread TempDB across atleast as many equal sized files as there are COREs or CPUs."  That's a fairly extreme statement.  TempDB files are used in a round-robin fashion.  Having more of them than you have cores (possible threads) would be worthless.  Having an equal number to the number of cores is more often than not, excessive as well.  And you can expect them to all grow to roughly the same size as they will all eventually serve the most demanding statement that will come through.  Most recommendations I've seen show that one tempdb file per 2-4 cores is sufficient. The info on growth is very good.

  • Anonymous
    June 30, 2015
    The comment has been removed

  • Anonymous
    July 07, 2017
    Attractive section of content. I just stumbled upon your blog and in accession capital to claim that I get actually loved account your blog posts.Any way I'll be subscribing for your augment or even I success you get entry to consistently fast.