TempDB best practices - Doubt

  • Hello guys,

    I have two doubt:

    -- 1 script:

    SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],

    cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_in_bytes/1024 AS [Physical Memory (MB)], sqlserver_start_time

    FROM sys.dm_os_sys_info;

    return: Logical CPU Count:4Hyperthread Ratio:1Physical CPU Count: 4Physical Memory (MB): 8388088

    what is my doubt?

    1. I need put more 3 datafile for my tempdb? because i have one (mdf and ldf) - default.

    2. About the Initial Size and autogrow?? how can i define this options?

  • someone to volunteer?

  • LOVER OF SQL (9/22/2013)


    someone to volunteer?

    Sure. Please see the following links.

    https://www.google.com/#q=paul+randal+number+of+tempdb+files

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

    --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)

  • and about the size of them? how can i calculate that?

  • How big does TempDB get? Take that size, divide by the number of files, add a bit extra for safety.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • LOVER OF SQL (9/22/2013)


    and about the size of them? how can i calculate that?

    To be honest, I thought my post might inspire you to hit Google so that you can see many different recommendations. 😉

    Start off with what Gail suggested. Make sure that growth is in MB and not % and that it's not set too low. Here's a couple of the better links for what that's all about.

    https://www.google.com/#q=kimberly+tripp+vlf

    http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

    --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)

  • Yes... but lets suppose that I am installing at the moment and tempdb is small.. (2,3,4mb), in other words, is not a big... how can i calculate this (for each datafile then I add...)

  • yes, but the problem at the moment is because my tempdb is thin.... small.. (that is new project - SQL Server)... Hardware, OS, Database... and my tempdb is small.. you understand?

  • LOVER OF SQL (9/22/2013)


    yes, but the problem at the moment is because my tempdb is thin.... small.. (that is new project - SQL Server)... Hardware, OS, Database... and my tempdb is small.. you understand?

    It's a bit of a swag, then. If it were me, I'd set it to 1,000MB with 500MB growth for the MDF file and 500MB with 500MB growth for the LDF file and then monitor it as the system grows.

    --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)

  • 1,000MB equals = 1GB? or 1MB?

    and if I have 4 processor (physical) I need put more 3 datafile? because exist mdf (default).

    You sad: "500MB with 500MB growth for the LDF file and then monitor it as the system grows. " but, is not necessary I am add ldf files all right? only mdf for processor..

    Jeff Moden,

    How can i allow/endrose your answer in this topic?

  • LOVER OF SQL (9/23/2013)


    1,000MB equals = 1GB? or 1MB?

    and if I have 4 processor (physical) I need put more 3 datafile? because exist mdf (default).

    You sad: "500MB with 500MB growth for the LDF file and then monitor it as the system grows. " but, is not necessary I am add ldf files all right? only mdf for processor..

    Jeff Moden,

    How can i allow/endrose your answer in this topic?

    Do not add more LDF files. LDF files operate in a serial cyclic manner and there's no benefit.

    And obviously 1,000MB <> 1MB. 1,000MB = 1GB. If you have 4 processors, you could start 4 MDF files at 500MB with 500MB growth. Since you have no history on TempDB usage on this system, that's also a swag.

    --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)

  • " If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?

    and about: "files at 500MB with 500MB growth."

    size 500MB or 1GB for ndfs?

    500MB growth - all right!

  • LOVER OF SQL (9/23/2013)


    " If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?

    and about: "files at 500MB with 500MB growth."

    size 500MB or 1GB for ndfs?

    500MB growth - all right!

    Like I said, it's a total swag because you have no history on the system. If you can easily tolerate 4GB of MDF/LDF, the 1GB each for the MDF and the 3 NDFs would be fine. It will certainly delay any autogrowth and preallocating TempDB to that size will certainly not hurt anything unless you're extremely squeezed for disk space.

    --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)

  • I agree with Jeff. It's a guess, perhaps WAG, if you have no history.

    2GB (4 x 500MB) or 4GB (4 x 1GB) is a decent guess if you can spare the space. I'd also do a 500MB or 1GB log and monitor.

    5GB is not a lot of space these days and even if it's overkill, it prevents issues. I would monitor usage and watch it over weeks/months.

  • LOVER OF SQL (9/23/2013)


    " If you have 4 processors, you could start 4 MDF" (in the case, i'll have 5 datafiles all right? (4 for ndf) and (1 mdf-default), ok?

    No. 4 data files.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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