Home Forums SQL Server 2016 SQL Server 2016 - Administration Splitting TempDB across multiple files RE: Splitting TempDB across multiple files

  • December 28, 2017 at 1:30 am

    #1973678

    Tava - Wednesday, December 27, 2017 8:21 PM

    They will be production backups and configurations will vary. E.g Simple recovery model will be in SYSTEST/UAT while PROD will have FULL.

    Datavolume and transaction counts will obviously reduce in these env.

    Normally Tempdb can be splitted across multiple data files, It'll help to balance the load on Tempdb, this is one case.
    You can refer this article,

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/db90e985-af4b-44a1-9ff6-9830ca703c38/splitting-tempdb-into-multiple-data-files?forum=sqldatabaseengine

    But there are some debates who are all suggesting to have one data file per processor for Tempdb in the DB server.
    https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

    So to conclude, Your Production,UAT,Preprod will need to be have a same setup of Tempdb, which will help you to test / replicate any other future activities on these servers.

    From my end, I can say that In our project we'd splitted our Tempdb into multiple data files with a log file to overcome the contention / concurrent / to balance the overload on Tempdb.