Post reply

Tempdb files - Load test

  • July 15, 2013 at 1:09 pm

    #275995

    How to arrive at the number of tempdb files needed for a SQL 2008 2 processor server. Everyone mentions about having 1/4 or 1/2 or equal to the number of cpu's. But, finally it varies depending on the setup and the load. So, how can i perform a load test and arrive at an optimal number of tempdb files?

  • July 15, 2013 at 1:58 pm

    #1632664

    look at Paul Randal's note on this subject:

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

  • July 15, 2013 at 3:35 pm

    #1632688

    I have read that and I understand that its not necessary to have multiple tempdb files. But my question was, how do i come to that conclusion whether mutltiple files or not?

  • July 15, 2013 at 10:15 pm

    #1632741

    balasach82 (7/15/2013)

    I have read that and I understand that its not necessary to have multiple tempdb files. But my question was, how do i come to that conclusion whether mutltiple files or not?

    Look for the latches mention in that article and test. If the performance goes down or doesn't go up when you add the files, then you don't need them to begin with.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 15, 2013 at 10:55 pm

    #1632750

    No preference with tempdb files per core ration, until we face tempdb bottleneck. Usually it would start with Latch waits for allocation contention.

Viewing 5 posts - 1 through 4 (of 4 total)

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