TempDb - Split or Add

  • Hi,

    I am hoping someone can answer the question of whether it is better to split tempdb or add another tempdb file? Which is better and why?

  • Since you can't add a second tempdb, adding a file is the way to go. Here's a good review[/url] on how to pick tempdb file settings. There are a bunch of other links there too.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • The reason for asking is that I see NDF and MDF in various examples:

    ALTER DATABASE [tempdb] ADD FILE (NAME = tempdb_2, FILEGROWTH = 10%, MAXSIZE = UNLIMITED, SIZE=300MB , FILENAME = 'C:\MSSQL\MyTempDB\tempdev_Data02.mdf')

    ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_Data02', FILENAME = N'C:\MSSQL\MyTempDB\tempdev_Data02.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )

  • ndfs are just additional files.

    ALWAYS have EQUAL SIZED AND GROWTH FACTOR tempdb files, no matter how many you have.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Why do some people end their second etc. tempdb files with NDF and others with MDF?

  • You could call the files .txt and it would work the same. SQL doesn't care in the slightest what the extensions of its data files are.

    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
  • What Gail said.

    I don't know how that started, but I've seen examples of that approach to naming files going back to when I started working with SQL Server 4.2... Sybase SQL Server 4.2. I suspect it's a standard that someone at Sybase started and everyone emulated.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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