Setting All Tempdb Data Files to the Same Size

  • Comments posted to this topic are about the item Setting All Tempdb Data Files to the Same Size

  • Hi Aldo,

    rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.

    e.g. On 40GB drive set each file to 10GB

    Thansk

  • Hi Aldo,

    rather than set a growth figure would it not be better to set the size so all space is taken up on the drive.

    e.g. On 40GB drive set each file to 10GB

    Thansk

  • Though im not going to argue with your analysis, the premise of the article

    "Best practices call for tempdb to have one datafile per processor (core)"

    is based on a myth

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

    "So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason"

    My emphasis.



    Clear Sky SQL
    My Blog[/url]

  • Although you may not need a one-to-one match for TempDB files and CPU cores, if you do think TempDB is causing a bottleneck, I'd certainly recommend increasing the number of TempDB files from 1. On a number of our servers I've set up 2 files for TempDB (some servers have 2 cores, some have many more) and even just moving to 2 files seems to improve performance.

    I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!

  • This one definitely depends on your situation. Most of the time I break the TempDB into only four or eight separate equally sized files with no auto grow. This has been a good general practice for my environment, but each server can be different depending on the type of data on them so testing is the best option. Also in the bulk of the tests I did a couple years ago I found no significant gains in splitting the TempDB files between different Luns/Spindles.

  • Hello all.

    Are those objections true for virtual servers? May be then situation it is more complex since IO is "virtual"?

  • jts_2003 (7/19/2010)


    I also agree with the comment of making the TempDB files as large as possible - the last thing you want is a delay to a transaction because TempDB has to autogrow. But I wouldn't suggest using the whole disk, maybe 80% - that way you've got room to grow in an emergency while you work out what caused all the space to be used up!

    There's something funky about this logic. You're not increasing space in an emergency; you're artificially limiting space to begin with, and adding the overhead of an autogrow in an emergency. If you think a query might pass that 80% mark, wouldn't it be better to dedicate 100% of the disk to begin with?

  • What is the point of having multiple files ON THE SAME DRIVE c:?

  • obulay-657245 (7/19/2010)


    What is the point of having multiple files ON THE SAME DRIVE c:?

    This is straight from Paul Randal's blog that another user posted a link to abover

    PS To address a comment that came in - no, the extra files don't *have* to be on separate storage. If all you're seeing it PAGELATCH contention, separate storage makes no difference as the contention is on in-memory pages. For PAGEIOLATCH waits, you most likely will need to use separate storage, but not necessarily - it may be that you need to move tempdb itself to different storage from other databases rather than just adding more tempdb data files. Analysis of what's stored where will be necessary to pick the correct path to take.

    Good article, but great discussion. I just worked with some Microsoft folks on thier SCOM team for a deployment of thier software and they insisted on 1 TempDB per processor and for it to take up most of the drive 80%. It is good to now know what to look for to see if the recommendations were over kill or if they were valid.

    It looks likethe answer is with most things SQL Server ....It Depends

    Twitter: @SQLBalls
    Blog: http://www.SQLBalls.com

  • Thanks for sharing the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I would encourage everyone following this thread to heed what Paul Randal posted in his blog give above. Tempdb should have no more than 8 datafiles. You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.

  • Tim-153783 (7/19/2010)


    You should also make sure that the account running the sql server service has the local security policy right, Perform Volume Maintenance Tasks. This will allow for instant file initialization rather than waiting for the format to finish.

    Remember this is for NON-TDE databases, SQL instances that use TDE cannot use instant initialization for the Tempdb as it is encrypted. As well any database on the instance that is using TDE cannot use Instant initialization as well.

    Twitter: @SQLBalls
    Blog: http://www.SQLBalls.com

  • I agree. The article only addresses the sizing of each datafile with respect to the other datafiles on the tempdb filegroup.

    Thanks.

  • Thanks you for the comment. It is clear that there are two schools of thought when it comes to the right number of datafiles in tempdb. Microsoft recommends the datafile per core setting, and I have read articles that report major improvements when one datafile per core is used over the single datafile configuration. The purpose of the article is not to argue in favor of such practice, but to provide the steps to implement it.

    I will read the article you point to. A great experiment would be to set up both configurations and compare.

    Thanks again.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply