To improve your SQL Server performance or relive TempDB page (wirte) blocking consider splitting the TempDB Data file into the amount of logical processors in the server.
As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
http://support.microsoft.com/kb/2154845
Use the following script to split out TempDB
USE master
GO
CREATE TABLE #numprocs
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @CORES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT
INSERT INTO #numprocs
EXEC xp_msver
SELECT @CORES = Internal_Value FROM #numprocs WHERE [Index] = 16
PRINT @CORES
SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH
SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')
SELECT @SIZE = size FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SET @SIZE = @SIZE / 128
SELECT @GROWTH = growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
SELECT @ISPERCENT = is_percent_growth FROM master.sys.master_files WHERE database_id = 2 AND FILE_ID = 1
WHILE @CORES > @FILECOUNT
BEGIN
SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
ADD FILE
(
FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@CORES as CHAR)) + '.ndf'',
NAME = tempdev' + RTRIM(CAST(@CORES as CHAR)) + ',
SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
IF @ISPERCENT > 0
SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
EXEC(@SQL_SCRIPT)
SET @CORES = @CORES - 1
END
GO
DROP TABLE #numprocs