locked
What is the best practice for configuring tempdb? RRS feed

  • Question

  • need help in configuring TEMPDB on SQL Server 2008 R2.

    What should be allocated file size for tempdb? Also how many files do we define for tempdb? Does it have any relation to no. of processor cores?

    Tuesday, June 26, 2012 7:27 AM

Answers

  • Recommendations for TempDB files placement :-

    • Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
    • Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
    • Make each data file the same size; this allows for optimal proportional-fill performance.
    • Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
    • Put the tempdb database on disks that differ from those that are used by user databases.
    •  No. Of CPU v/s Data Files Recommendation

    No. of Cores

    Data files recommended

    <8

    = No. of Cores

    >=8 to <32

    = No. of Cores/2

    >=32

    = No. of Cores/4

    Helpful links :-

    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Hope it helps!

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!

    • Proposed as answer by PhyDataDba Monday, December 2, 2013 3:32 PM
    • Marked as answer by Mayur-DEW Friday, July 27, 2018 1:31 PM
    Tuesday, June 26, 2012 5:35 PM
  • need help in configuring TEMPDB on SQL Server 2008 R2.

    What should be allocated file size for tempdb? Also how many files do we define for tempdb? Does it have any relation to no. of processor cores?

    This is one of the biggest "It Depends..." that exists in SQL Server.  It all depends on how your SQL Server uses tempdb.  If you don't make use of tempdb then you won't have a significant amount of contention on the allocation bitmap pages and therefore won't need as many files to alleviate the contention.  The whole point behind having multiple files is that each file gets separate GAM, SGAM, and PFS allocation pages, so as round-robin allocation and proportional fill are used to write data to the data files, you spread the access to these pages across multiple files and reduce the contention on them.  The size question is strictly based on your specific environment, just take whatever size tempdb needs to be in total, and then divide that by the number of files being created, and create each file exactly the same size.  Then make sure that you set AutoGrow for the files exactly the same as well so that they grow the same way and maintain the same size, otherwise one file could become an allocation hot spot if it grows larger and has a larger proportion of free space in it.

    Last year at PASS 2011 Bob Ward, one the Sr Escalation Engineers for SQL, made the following recommendation which will be updated in the Microsoft references that other people provided on this thread:

    As a general rule, if the number of logical processors is less than 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

    The other thing I'd recommend if you have excessive contention at 8+ files is to follow the last part of that general recommendation, and make changes to the workload and code because the excessive use of tempdb is still a bottleneck that in most cases can be fixed by changing how your code works and doing some basic tuning.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Shulei Chen Monday, July 9, 2012 2:54 AM
    • Marked as answer by amber zhang Monday, July 9, 2012 4:01 AM
    Wednesday, June 27, 2012 1:48 PM

All replies

  • Please check-out below MSDN article

    Optimizing tempdb Performance

    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Tuesday, June 26, 2012 7:34 AM
  • Have a look at the following recommendations...

    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    ...regarding the number of tempdb files matching the number of cores it's bit more complicated than that, have a look at Paul Randal's excellent article regarding the number of tempdb files versus cores...

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem


    • Edited by moort Tuesday, June 26, 2012 7:36 AM
    Tuesday, June 26, 2012 7:34 AM
  • HI Mayur

    Please check the below links

    http://bradmcgehee.com/wp-content/uploads/presentations/SSC06%20How%20to%20Optimize%20TEMPDB%20Performance.pdf

    http://msdn.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx

    http://www.sqllion.com/2009/05/optimizing-tempdb-in-sql-server-2005/

    Tuesday, June 26, 2012 7:37 AM
  • Recommendations for TempDB files placement :-

    • Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
    • Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
    • Make each data file the same size; this allows for optimal proportional-fill performance.
    • Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
    • Put the tempdb database on disks that differ from those that are used by user databases.
    •  No. Of CPU v/s Data Files Recommendation

    No. of Cores

    Data files recommended

    <8

    = No. of Cores

    >=8 to <32

    = No. of Cores/2

    >=32

    = No. of Cores/4

    Helpful links :-

    http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Hope it helps!

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!

    • Proposed as answer by PhyDataDba Monday, December 2, 2013 3:32 PM
    • Marked as answer by Mayur-DEW Friday, July 27, 2018 1:31 PM
    Tuesday, June 26, 2012 5:35 PM
  • need help in configuring TEMPDB on SQL Server 2008 R2.

    What should be allocated file size for tempdb? Also how many files do we define for tempdb? Does it have any relation to no. of processor cores?

    This is one of the biggest "It Depends..." that exists in SQL Server.  It all depends on how your SQL Server uses tempdb.  If you don't make use of tempdb then you won't have a significant amount of contention on the allocation bitmap pages and therefore won't need as many files to alleviate the contention.  The whole point behind having multiple files is that each file gets separate GAM, SGAM, and PFS allocation pages, so as round-robin allocation and proportional fill are used to write data to the data files, you spread the access to these pages across multiple files and reduce the contention on them.  The size question is strictly based on your specific environment, just take whatever size tempdb needs to be in total, and then divide that by the number of files being created, and create each file exactly the same size.  Then make sure that you set AutoGrow for the files exactly the same as well so that they grow the same way and maintain the same size, otherwise one file could become an allocation hot spot if it grows larger and has a larger proportion of free space in it.

    Last year at PASS 2011 Bob Ward, one the Sr Escalation Engineers for SQL, made the following recommendation which will be updated in the Microsoft references that other people provided on this thread:

    As a general rule, if the number of logical processors is less than 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

    The other thing I'd recommend if you have excessive contention at 8+ files is to follow the last part of that general recommendation, and make changes to the workload and code because the excessive use of tempdb is still a bottleneck that in most cases can be fixed by changing how your code works and doing some basic tuning.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Shulei Chen Monday, July 9, 2012 2:54 AM
    • Marked as answer by amber zhang Monday, July 9, 2012 4:01 AM
    Wednesday, June 27, 2012 1:48 PM