locked
How do I determine if Tempdb needs more than one datafile??? RRS feed

  • Question

  • Just like the subject says.

    Alan

    Tuesday, March 18, 2014 9:30 PM

Answers

  • Hi,

    Generally if problem is with tempdb you might come to know with slowness of the query and overall performance.Then take a look at waiting tasks using

    SELECT session_id, wait_duration_ms, resource_description
    FROM sys.dm_os_waiting_tasks
    WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'

    If in above output you can find tempdb waiting for PAGEIO latch then its advisable to a tempdb data file and see if there is a relief.

    Below link will be helpful

    Tempdb

    To read about tempdb and how to deal with issues arising from it.I recommend you to go through below article

    Working with tempdb


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Tuesday, March 18, 2014 9:46 PM
    Answerer
  • I guess I should have said this. With new data storage i.e. SSD I have heard that there is no longer a need for multiple tempdb files.

    Alan

    You are correct and this is what Paul Randal also says in below article but sometimes if you find lot of pageio latch and SGAM contention it is good to add it.

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by anaylor01 Tuesday, March 18, 2014 10:30 PM
    Tuesday, March 18, 2014 10:25 PM
    Answerer

All replies


  • The general rule 1 data file per logical or physical processor

    http://blogs.msdn.com/b/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx

    If you are already following the general rule then you've think of optimization and capacity planning

    Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use thesys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see Troubleshooting Insufficient Disk Space in tempdb.


    Reference:-

    http://msdn.microsoft.com/en-us/library/ms345368.aspx

    http://msdn.microsoft.com/en-us/library/ms175527.aspx

    -Prashanth


    Tuesday, March 18, 2014 9:43 PM
  • Don't expect miracles just by changing tempdb files.

    On the other hand, placing tempdb data and log on separate independent drives does help with performance.

    BOL: "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."

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

    Optimization: http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    • Edited by Kalman Toth Wednesday, March 19, 2014 8:50 AM
    Tuesday, March 18, 2014 9:45 PM
  • Hi,

    Generally if problem is with tempdb you might come to know with slowness of the query and overall performance.Then take a look at waiting tasks using

    SELECT session_id, wait_duration_ms, resource_description
    FROM sys.dm_os_waiting_tasks
    WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '2:%'

    If in above output you can find tempdb waiting for PAGEIO latch then its advisable to a tempdb data file and see if there is a relief.

    Below link will be helpful

    Tempdb

    To read about tempdb and how to deal with issues arising from it.I recommend you to go through below article

    Working with tempdb


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Tuesday, March 18, 2014 9:46 PM
    Answerer
  • I guess I should have said this. With new data storage i.e. SSD I have heard that there is no longer a need for multiple tempdb files.

    Alan

    Tuesday, March 18, 2014 10:21 PM
  • I guess I should have said this. With new data storage i.e. SSD I have heard that there is no longer a need for multiple tempdb files.

    Alan

    You are correct and this is what Paul Randal also says in below article but sometimes if you find lot of pageio latch and SGAM contention it is good to add it.

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by anaylor01 Tuesday, March 18, 2014 10:30 PM
    Tuesday, March 18, 2014 10:25 PM
    Answerer