Post reply

Optimal size for TempDB files?

  • January 2, 2014 at 7:16 am

    #284087

    Today as a colleague and I were reviewing our TempDB configuration for a server he mentioned he had heard that the optimal size for TempDB files are 4GB, this is to reduce/prevent page splits. This intrigued me, as the only "page splits" I've ever known about were at the actual page level and is an indication of fragmentation.

    A quick search on google did not turn up any results so I'm posting here to see if anyone else has heard of this and could shed some light?

  • January 2, 2014 at 7:19 am

    #1676897

    Nope.

    The optimal size for TempDB files is large enough that they don't have to grow under regular operations.

    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

  • January 2, 2014 at 10:16 am

    #1676990

    I'll add that the only page splits I'm familiar with is related to fragmentation where the record cannot fit on that page and needs a new one next to it.

    as far as tempDB goes, Gail is right, there's no set size for TempDB. Let it run for a bit and see where it stops growing. I would then personally shrink it down then regrow it in what ever size chunks i need to get to about 50 VLF's.

    For a brand new database, it would all depend on what the database is used for, expected growth, amount of data change expected, and what recovery model I'm going to use to decided where to start it. It'll grow until it no loner needs to grow.

    .

  • January 2, 2014 at 4:21 pm

    #1677083

    Is there any downside to having TempDB files that are larger than needed other than that they occupy more drive space than necessary?

  • January 2, 2014 at 6:36 pm

    #1677097

    As long as your VLF's are in check... no.

    .

  • January 2, 2014 at 10:35 pm

    #1677111

    VLFs only apply to log files.

    No, there's no downside to having the TempDB data files (which I assume is what you were asking about) be larger than strictly necessary. It gives you breathing room in case some query abuses TempDB

    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

  • January 3, 2014 at 6:57 am

    #1677250

    Yeah... I have no clue what i was thinking with the VLF's. It's been a long day ^.^'

    .

Viewing 7 posts - 1 through 6 (of 6 total)

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