To improve query performance when you've found tempdb to be a performance bottleneck, you can create multiple data files of equal size. It is recommended that you add one file per CPU (per core, not per socket). The below script will do this for you. It is written as a stored procedure as I like to keep all of my scripts in an admin-type database. You will probably only run this once per instance though.
To determine how big each file should be, look at the size of your tempdb's data file which by default is named tempdb.mdf. You should check the file size after your system has been running for a while after a reboot or a restart of the SQL Server service. For instance, we typically reboot our database servers once a month due to the monthly Microsoft security patches. In my environment, I would check the file size about 30 days after the reboot so that I know it is at its typical maximum value.
As an example, if the file size is 8 GB and you've got 8 CPUs, then I would suggest passing a minimum of 1024 to the stored procedure as the inputs accept megabytes.
You should run the stored procedure after a reboot or a restart of the SQL Server service so that tempdb is at its smallest, which should avoid any errors.
If you've already set the initial size of tempdb to be greater than the value passed to the stored procedure, it will error as you can not reduce the setting with ALTER DATABASE. You must first set the initial size to a lower value. I have only seen one way to do this, which can be found in this thread. Check out rklime's post on 10-23-2007 at 10:05 AM.
This tempdb architecture is described in SQL Server Books Online. Here is the online reference for the article.
Although this TechNet article is long, it is a must read for database administrators as it goes into great detail about tempdb.
Here is the stored procedure:
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_AddTempdbFiles
--
-- AUTHOR : Tara Kizer
--
-- DATE : May 13, 2008
--
-- INPUTS : @size - size of the tempdb files in megabytes
-- @growth - growth increment of the tempdb files in megabytes
--
-- OUTPUTS : None
--
-- RETURN CODES : None
--
-- DESCRIPTION : Adds one tempdb file per CPU
--
-- EXAMPLE : EXEC isp_AddTempdbFiles @size = 1024, @growth = 200
----------------------------------------------------------------------------------------------------
ALTER PROC [dbo].[isp_AddTempdbFiles]
(@size int, @growth int)
AS
SET NOCOUNT ON
DECLARE
@regOutput varchar(20), @numProcs int, @sql nvarchar(4000), @tempdbDataFileCount tinyint, @tempdbDataFileSize int,
@tempdbFirstLogicalName sysname, @tempdbDataFileId tinyint, @i tinyint, @j tinyint,
@tempdbMissingDataFileCount tinyint, @tempdbLogicalName sysname, @tempdbFirstPhysicalPath nvarchar(260)
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @regOutput OUTPUT
SET @numProcs = @regOutput
SELECT @i = 2, @j = 2, @tempdbDataFileCount = COUNT(*), @tempdbDataFileId = MIN(file_id)
FROM tempdb.sys.database_files
WHERE type = 0
SET @tempdbMissingDataFileCount = @numProcs - @tempdbDataFileCount
IF @tempdbDataFileCount <> @numProcs
BEGIN
SELECT
@tempdbFirstLogicalName = name,
@tempdbFirstPhysicalPath =
SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name )) + 1)
FROM tempdb.sys.database_files
WHERE file_id = @tempdbDataFileId
SET @tempdbDataFileId = 0
-- file exists, expand it, if necessary. to make sure they will all be the same
WHILE @tempdbDataFileCount <> 0
BEGIN
SELECT @tempdbDataFileId = MIN(file_id)
FROM tempdb.sys.database_files
WHERE type = 0 AND file_id > @tempdbDataFileId
SELECT @tempdbLogicalName = name, @tempdbDataFileSize = size
FROM tempdb.sys.database_files
WHERE file_id = @tempdbDataFileId
IF @size*1024/8 > @tempdbDataFileSize
BEGIN
SET @sql = 'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @tempdbLogicalName + ''', SIZE = '
SET @sql = @sql + CONVERT(varchar(10), @size) + 'MB, FILEGROWTH = ' + CONVERT(varchar(10), @growth) + 'MB)'
--PRINT @sql
EXEC (@sql)
END
SET @tempdbDataFileCount = @tempdbDataFileCount - 1
END
-- missing files
WHILE @tempdbMissingDataFileCount <> 0
BEGIN
WHILE 1 = 1
BEGIN
IF EXISTS
(
SELECT *
FROM tempdb.sys.database_files
WHERE name = @tempdbFirstLogicalName + CONVERT(varchar(3), @i)
)
SET @i = @i + 1
ELSE
BREAK
END
WHILE 1 = 1
BEGIN
IF EXISTS
(
SELECT *
FROM tempdb.sys.database_files
WHERE physical_name = @tempdbFirstPhysicalPath + @tempdbFirstLogicalName + CONVERT(varchar(3), @j) + '.ndf'
)
SET @j = @j + 1
ELSE
BREAK
END
SET @sql = 'ALTER DATABASE tempdb ADD FILE (NAME = N''' + @tempdbFirstLogicalName + CONVERT(varchar(3), @i)
SET @sql = @sql + ''', FILENAME = N''' + @tempdbFirstPhysicalPath + @tempdbFirstLogicalName + CONVERT(varchar(3), @j)
SET @sql = @sql + '.ndf' + ''', SIZE = ' + CONVERT(varchar(10), @size) + 'MB, FILEGROWTH = '
SET @sql = @sql + CONVERT(varchar(10), @growth) + 'MB)'
--PRINT @sql
EXEC (@sql)
SET @tempdbMissingDataFileCount = @tempdbMissingDataFileCount - 1
END
END