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 = 'SYSTEMCurrentControlSetControlSession ManagerEnvironment',
      @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

[@more@]

ITPub博客

Optimizing tempdb

最新文章