none
TempDB Script

    问题

  • I have a TempDB1 and TempDB2 set up for SQL server.  I was looking for a script to have SQL Server 2008 R2 use this for performance.  I found this article but I am not sure if it will do the job on the solution.  It looks like it is only setting up on tempdb but I could be wrong. 

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/683051f3-a38f-4ab9-aec2-0f2f8214eb45

    Thanks in advance.

    2012年7月6日 12:02

答案

  • Hi,

    Yes, you can use similar script but add size to this as if you are adding more than data file than fie size of the files should be same. Suppose volums E:, F: and G: are for tempdb

    USE [master]
    GO
    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'E:\tempdb\tempdb.mdf' )
    GO
    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'F:\tempdb\tempdb.mdf' )
    GO
    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev1', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'g:\tempdb\tempdb.ldf' )
    GO
    New file we added instantly, but location will be changed after SQL server service restart. 

    - Chintak (My Blog)

    • 已标记为答案 CSharp0101 2012年7月6日 13:09
    2012年7月6日 13:06

全部回复

  • The link you posted is an example of how to create a tempdb file for each core on the server which is the recommended setup for the tempdb for optimal performance.  If you setup your tempdb files as the same size SQL Server will automatically spread the processing equally over the files.
    2012年7月6日 12:21
  • Hi,

    Based on your description ("I have a TempDB1 and TempDB2 set up for SQL server"), it looks like you have two Tempdb data files logical filename TempDB1 and TempDb2. 

    Now, there is a recommendation for creating once file per CPU. But this should be done as needed.

    You can start with one tempdb data file per may be two (may be more) logical cpu. Then you can measure the contention on tempdb and if you are able to find it, you can should increase files upto number of CPU.

    You can run query multiple times in some peak period to detect contention..

    ;WITH tasks 
         AS (SELECT session_id, 
                    wait_type, 
                    wait_duration_ms, 
                    blocking_session_id, 
                    resource_description, 
                    PageID = Cast(RIGHT(resource_description, Len( 
                                  resource_description) - 
                                           Charindex(':', resource_description, 3 
                                           )) AS INT) 
             FROM   sys.dm_os_waiting_tasks 
             WHERE  wait_type LIKE 'PAGE%LATCH_%' 
                    AND resource_description LIKE '2:%') 
    SELECT session_id, 
           wait_type, 
           wait_duration_ms, 
           blocking_session_id, 
           resource_description, 
           ResourceType = CASE 
                            WHEN pageid = 1 
                                  OR pageid % 8088 = 0 THEN 'Is PFS Page' 
                            WHEN pageid = 2 
                                  OR pageid % 511232 = 0 THEN 'Is GAM Page' 
                            WHEN pageid = 3 
                                  OR ( pageid - 1 ) % 511232 = 0 THEN 'Is SGAM Page'
                             ELSE 'Is Not PFS, GAM, or SGAM page' 
                          END 
    FROM   tasks;  


    - Chintak (My Blog)

    2012年7月6日 12:29
  • I also ran accross this:

    USE master
    go
    
    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘E:tempdb.mdf’)
    go
    
    ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘E:templog.ldf’)
    go


    Since I have the TempDB drives created, would that script tell SQL to use the specified drive folders?

    Since I have two TempDB's created do I need to duplicate that code above giving it the new name of drive?

    2012年7月6日 12:47
  • Hi,

    Yes, you can use similar script but add size to this as if you are adding more than data file than fie size of the files should be same. Suppose volums E:, F: and G: are for tempdb

    USE [master]
    GO
    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'E:\tempdb\tempdb.mdf' )
    GO
    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'F:\tempdb\tempdb.mdf' )
    GO
    ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev1', SIZE = 5120000KB,  FILEGROWTH = 1024000KB, FILENAME = 'g:\tempdb\tempdb.ldf' )
    GO
    New file we added instantly, but location will be changed after SQL server service restart. 

    - Chintak (My Blog)

    • 已标记为答案 CSharp0101 2012年7月6日 13:09
    2012年7月6日 13:06
  • Awesome! Thanks for your help!
    2012年7月6日 13:10
  • Ran into an issue stating it cant find my file path even though the path does exist.

    2012年7月6日 13:46
  • Can you post your SQL_Statement?
    2012年7月6日 14:27
  • I allocated more space to the Temp db so now it works.

    I should not have added this part:

    SIZE = 5120000KB,  FILEGROWTH = 1024000K

    2012年7月6日 15:49
  • Hi,

    Actually, we should make sure that each data file is of equal size, So I have added this as an example.


    - Chintak (My Blog)

    2012年7月6日 16:03