Post reply

Number of TempDB's across two volumes

  • August 26, 2014 at 2:09 am

    #298856

    Hello, I am working out the set up for a new server we have in Amazon EC2.

    The server will have 4 virtual CPU's (or 4 cores in physical terms i think)so according to the rule of thumb I would use four TempDB's to help prevent logical contention.

    Now suppose I have two different RAID volumes available on the instance and I wanted to put some TempDB's on both volumes to minimise any physical TempDB contention would I still just put four TempDB files on each volume (the same size and growth rate of course) so their would be a total of eight TempDb files across two RAID volumes?

    thanks

  • August 26, 2014 at 2:43 am

    #1740947

    You can't have more than one TempDB per instance.

    If you mean files, 4 files is fine to start with. Add more and spread across volumes if you see allocation contention or IO contention.

    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 26, 2014 at 12:57 pm

    #1741120

    GilaMonster (8/26/2014)

    4 files is fine to start with. Add more and spread across volumes if you see allocation contention or IO contention.

    Thank you for the reply, I did mean files not databases.

    So just to make it clear in my head. If I was going to use four files as a starting point based on the core count would it be two on one volume and two on the other volume or would it be four on one volume and four on the other volume?

    thanks

  • August 26, 2014 at 1:57 pm

    #1741130

    Total of 4. Place them on the same Drive.

  • August 26, 2014 at 2:45 pm

    #1741137

    and if testing shows their is IO contention add four more to another volume, is that what you meaning?...thanks

  • August 26, 2014 at 2:54 pm

    #1741138

    UncleBoris (8/26/2014)

    So just to make it clear in my head. If I was going to use four files as a starting point based on the core count would it be two on one volume and two on the other volume or would it be four on one volume and four on the other volume?

    First, don't base on core count. TempDB files = cores is very old advice.

    If you decide to use four files, then you use four 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 6 posts - 1 through 5 (of 5 total)

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