locked
I have a sql server 2008. What is the best placement for the tempdb.mdf? RRS feed

  • Question

  • The sql server has three drives c, d and e. The c drive contains the program. The D drive contains the main db and the tempdb.mdf. The E drives contains all the logs. I've seen the disk queue for drive D spiked up to 50 sometimes. The majority of the read/writes came from maindb and the tempdb. I was wondering if it is a good idea to separate the tempdb from the maindb to another drive? Thanks.

    Friday, April 25, 2014 2:57 PM

Answers

  • Personally I always configure my servers so that the tempdb data file (tempdb.mdf) is on it's own independent drive.

    So something like the following:

    C: - OS
    D: - SQL Server data files (excluding tempdb)
    E: - SQL Server tempdb data file
    F: - SQL Server log files (including tempdb)

    I've always configured the E: drive in the above scenario as a RAID 1 or RAID 10 array.

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 4:15 PM
  • Much the same here, but I try to have tempdbs log on its own drive too.

    This helps in large load situations where there's a reasonable amount of heavy lifting going on, and both table and tempdb logs are being updated.

    Its also worth noting that putting data files/logs on different logical drives will only help when those logical drives also reside on separate physical drives. If they don't, then you're still using the same physical spindles and still waiting for the associated seeks anyway.

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 4:20 PM
  •  The E drives contains all the logs. I've seen the disk queue for drive D spiked up to 50 sometimes. The majority of the read/writes came from maindb and the tempdb. I was wondering if it is a good idea to separate the tempdb from the maindb to another drive? Thanks.

    Hello,

    Disk queue value you posted does not qualifies exactly which counter you are talking about.If you are getting instantaneous high value for Avg disk queue length it is not a issue but if this queue length is for long time and continues to be a straight line then it can be issue.Its always good practice to put tempdb on separate drive to gain performance.Please post the value which counter shows not the value shown by graph.Refer below link

    http://technet.microsoft.com/en-us/library/cc938625.aspx


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

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 5:26 PM
    Answerer
  • The following blog is on disk resources optimization:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    >if it is a good idea to separate the tempdb from the maindb to another drive? Thanks.

    Yes. That is a good idea.

    You can get an extra kick in performance if tempdb log placed on a dedicated drive.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, May 7, 2014 6:53 PM
    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 11:10 PM
  • The best way is to make your TempDB in seprate drive or outright split the TempDB in multiple physical hard disk, so its depends the size of TempDB.

    Note : do not use a virtual partition, you must use a physical partion so a new Hard Disk.

    You can look for the best practices of TempDB :

    http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dceea24c-7a53-4450-94cd-8327b5daa759/what-is-the-best-practice-for-configuring-tempdb?forum=sqldatabaseengine

    http://technet.microsoft.com/en-us/library/cc966545.aspx

    Friday, April 25, 2014 11:42 PM

All replies

  • Personally I always configure my servers so that the tempdb data file (tempdb.mdf) is on it's own independent drive.

    So something like the following:

    C: - OS
    D: - SQL Server data files (excluding tempdb)
    E: - SQL Server tempdb data file
    F: - SQL Server log files (including tempdb)

    I've always configured the E: drive in the above scenario as a RAID 1 or RAID 10 array.

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 4:15 PM
  • Much the same here, but I try to have tempdbs log on its own drive too.

    This helps in large load situations where there's a reasonable amount of heavy lifting going on, and both table and tempdb logs are being updated.

    Its also worth noting that putting data files/logs on different logical drives will only help when those logical drives also reside on separate physical drives. If they don't, then you're still using the same physical spindles and still waiting for the associated seeks anyway.

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 4:20 PM
  •  The E drives contains all the logs. I've seen the disk queue for drive D spiked up to 50 sometimes. The majority of the read/writes came from maindb and the tempdb. I was wondering if it is a good idea to separate the tempdb from the maindb to another drive? Thanks.

    Hello,

    Disk queue value you posted does not qualifies exactly which counter you are talking about.If you are getting instantaneous high value for Avg disk queue length it is not a issue but if this queue length is for long time and continues to be a straight line then it can be issue.Its always good practice to put tempdb on separate drive to gain performance.Please post the value which counter shows not the value shown by graph.Refer below link

    http://technet.microsoft.com/en-us/library/cc938625.aspx


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

    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 5:26 PM
    Answerer
  • The following blog is on disk resources optimization:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    >if it is a good idea to separate the tempdb from the maindb to another drive? Thanks.

    Yes. That is a good idea.

    You can get an extra kick in performance if tempdb log placed on a dedicated drive.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, May 7, 2014 6:53 PM
    • Marked as answer by tracycai Tuesday, May 13, 2014 1:32 AM
    Friday, April 25, 2014 11:10 PM
  • The best way is to make your TempDB in seprate drive or outright split the TempDB in multiple physical hard disk, so its depends the size of TempDB.

    Note : do not use a virtual partition, you must use a physical partion so a new Hard Disk.

    You can look for the best practices of TempDB :

    http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dceea24c-7a53-4450-94cd-8327b5daa759/what-is-the-best-practice-for-configuring-tempdb?forum=sqldatabaseengine

    http://technet.microsoft.com/en-us/library/cc966545.aspx

    Friday, April 25, 2014 11:42 PM
  • How do the drives organize? Meaning , are the physical or logical drives?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, April 27, 2014 5:35 AM