Microsoft SQL Server 2012 TempDB Best Practices

Here are my performance guidelines I've gleaned from experience as a DBA and the researching on the internet:

1. Move the primary TempDB.mdf to its own hard drive or the fastest drive in your system.
   • ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'T:\TempDB\Tempdb.mdf');
2. Add additional tempdb_N.mdf, one per spindle per physical cpu core.
   • As of 2013 a modern hard drive has one spindle. (A spindle is what the platters rotate around.)
   • Each core should have a fast spindle all to itself.
3. On each tempdb.mdf, set the Autogrowth to 1% and unlimited.
   • Some DBAs recommend setting autogrowth off and setting a maxium initial size to avoid fragmentation and 'pauses' on query executions. I disagree for the simple reason that in MSSQL 2012 tempdb makes good use of its internal bitmaps and will grow with the queries as needed. Never shrink the tempdb unless you need the space.
4. Move the primary TempDB.ldf it its own hard drive or the fastest drive in your system.
• ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'L:\TempDB\Tempdb.ldf');
5. Restart the server and verify your database changes: select [name], [physical_name] AS [CurrentLocation], [state_desc] from [sys].[master_files] order by [name]
6. Defragment the free space on all drives. I recommend Defraggler or Auslogics Defrag.