Processor Core

Split SQL TempDB per number of Processor Core

To achieve optimal tempdb performance, create multiple tempdb data file per processor core and limit the number of tempdb data files up to 8 if you have more than 8 CPU core. Key benefit of creating multiple tempdb per core is it enables SQL to split the I/O transactions across each data files and also prevent blocking when multiple CPU processors perform SQL operation simultaneously. – Log into SQL via SQL Server Management Studio – Click on New Query - On the query page copy and paste the script at the end of the post - click the blue check sign to validate the script - click Execute if validation passed – When the script runs successfully - The Messages section would display number of tempdb files now available.