stripe or split tempdb for 4 cores on processor

Is there a step by step guide to splitting up my tempdb file to have IO for my xeon quadcore?
FTGEAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Yes.. And T-SQL statements to do it here:

http://sqlsolace.blogspot.com/2007/11/moving-tempdb.html

Kindly make sure

1. That you keep all tempdb files into a new drive preferrably on a different disk spindle
2. Split tempdb into file size count equalling no. of processors..
In your case Quadcore processor and hence split tempdb into 4 files
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Also make sure that you pre-grow your tempdb files to some Max value for better performance instead of it growing over a period of time..
0
 
rk_india1Connect With a Mentor Commented:
To gain the maximum benefit from using multiple tempdb data files, Microsoft recommends that you create .25 to 1 data files per processor on the system. For example, on very large systems (16 or 32 processors), 10 tempdb files may be sufficient. However, all the files must be the same size.

"Placing each file on its own disk or spindle will further increase performance by reducing disk I/O contention, although it is not necessary for the reduction of contention on the GAM and SGAM pages.
"The allocation algorithm for GAM produces one extent at a time (8 contiguous pages) from the number of files in round robin fashion, while honoring the proportional fill. Therefore, if you have 10 equal-sized files, the first allocation is from File1, the second from File2, the third from File3, and so on.
"The resource contention of the PFS page is reduced in that 8 pages at a time are marked as FULL because GAM is allocating the pages.

The  thoughts are as follows:

"      Alter tempdb to have multiple files for any sql server that has more than 8 cpus.  
o      Create .25 to 1 data files per processor
§      Example ServerA  would have a tempdb with 8 data files instead of 1 data file.
o      While creating this other tempdb files preallocate space for all tempdb files to be 2gb each for data file and 6gb for log file.  With Auto growth being set to 256mb and unrestricted growth.

Example:

§      All 8 Data files are going on the P drive under P:\MSSQL2005\Data\
§      Each file size (initialization) should be set to 2048 mb and file growth set to 256mb
§      TempLog file to  J drive under J:\MSSQL2005\log
§      The file size (initialization) should be set to 6144mb with file growth at 256mb

Code Example :

USE master;
GO
ALTER DATABASE Tempdb MODIFY FILE
    (NAME = TempDev,SIZE = 2048MB, FILEGROWTH = 256MB, FileName = 'P:\MSSQL2005\Data\tempdb.mdf');
GO


--We will be utalizing one of the new drives for log space.  
--One of the log files that will reside on this drive is Tempdb log file.

ALTER Database Tempdb MODIFY File
     (Name = Templog, SIZE = 6144mb, FILEGROWTH = 256MB, FileName = 'J:\MSSQL2005\log\templog.ldf');
GO

Alter Database Tempdb add file
            (name='TempData1',
            FILENAME = 'P:\MSSQL2005\Data\TempData1.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)
     
      Alter Database Tempdb add file
            (name='TempData2',
            FILENAME = 'P:\MSSQL2005\Data\TempData2.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)

      Alter Database Tempdb add file
            (name='TempData3',
            FILENAME = 'P:\MSSQL2005\Data\TempData3.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)

      Alter Database Tempdb add file
            (name='TempData4',
            FILENAME = 'P:\MSSQL2005\Data\TempData4.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)

      Alter Database Tempdb add file
            (name='TempData5',
            FILENAME = 'P:\MSSQL2005\Data\TempData5.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)

      Alter Database Tempdb add file
            (name='TempData6',
            FILENAME = 'P:\MSSQL2005\Data\TempData6.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)

      Alter Database Tempdb add file
            (name='TempData7',
            FILENAME = 'P:\MSSQL2005\Data\TempData7.ndf',
            SIZE = 2048MB,
            FILEGROWTH = 256MB)


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.