How to Configure TempDB to improve performance

As best practice, you should create multiple copy of tempdb data files matching to number of logical processors to reduce tempdb contention problem also advise to maintain 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