Sometimes you face problem with tempdb contention due to complex queries and sorting issue, It advisable to optimize your tempdb after SQL server installation. It is also included in post installation and configuration best practice. you should create multiple copy of tempdb data files matching to number of logical processors to reduce tempdb contention problem
Tempdb optimization can you to improve query performance, now the question is how do you configure your tempdb to performance at optimum level.
How to Configure TempDB to improve performance
Multiple tempdb file with same in size, you can use trace flag 1117 to keep data file in same size. Maximum number of tempdb data file can be upto 8. You can query against sys.masster_files table to identify the number of datafile associated with tempdb.
To make it easy you can use below query to find out details of tempdb.
Find current tempdb configuration using below script
select DB_NAME(mf.database_id) database_name, mf.name logical_name, mf.file_id, CONVERT (DECIMAL (20,2), (CONVERT(DECIMAL, size)/128)) as [file_size_MB], CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%' WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' END AS [growth_in_increment_of] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT(DECIMAL(20,2) ,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024) WHEN 0 THEN CONVERT(DECIMAL(20,2) , (CONVERT(DECIMAL, growth)/128)) END AS [next_auto_growth_size_MB] , physical_name from sys.master_files mf where database_id =2 and type_desc= 'rows'
Find number of logical processors, Execute below script to get the cpu count (logical processors) on your existing environment.
SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
Add tempdb data files as per processor count from the above query
Now as suggested, Add the data files as per processor count using below ALTER DATABASE script
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\DBA\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) -- Update the data file location/Size/AutoGrowth
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3', FILENAME = N'D:\DBA\Data\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --Update the data file location/Size/AutoGrowth
GO
— ETC, add files as per processors count
No Reboot/Restart of SQL services is required for making the tempdb changes