Post reply

how many additional tempdb files can we add [max limit of tempd files ]

  • August 30, 2016 at 12:31 am

    #316830

    hi all,

    what is the max number of tempdb files for a 64 core cpu system which is having 768 gb of ram

    currently we are having 8 files we want to increase but not how mcuh max number is suggestive

    thanks

    rohitkumar

    Thanks
    Naga.Rohitkumar

  • August 30, 2016 at 4:57 am

    #1898261

    The max number of TempDB files is 32767.

    Why are you considering increasing the number of TempDB files? What problems are you trying to address?

    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

  • August 31, 2016 at 6:24 am

    #1898508

    we are currently having 8 tempdb files ,

    we feel that database performance issues happening and alternative for this to solve by adding more tempdb and reduce the load on the server

    -- what we want to know is for 64 core cpus machine how many number of tempdb files is max suggestable

    Thanks
    Naga.Rohitkumar

  • August 31, 2016 at 11:43 am

    #1898617

    naga.rohitkumar (8/31/2016)

    we feel that database performance issues happening and alternative for this to solve by adding more tempdb and reduce the load on the server

    Unless you've checked the cause of the performance problems and determined, without a doubt, that it is due to insufficient tempDB files (and there is ONE problem in that category), you're going to be wasting your time by doing so.

    So what leads you to believe that more TempDB files is the answer?

    what we want to know is for 64 core cpus machine how many number of tempdb files is max suggestable

    I wouldn't go above 4 unless I had a reason to do so. If I did have a reason to do so, then as many files as necessary to solve the contention problem, added in probably 4-file chunks.

    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 4 posts - 1 through 3 (of 3 total)

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