[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

stripe or split tempdb for 4 cores on processor

Posted on 2009-12-22
4
Medium Priority
?
1,255 Views
Last Modified: 2012-05-08
Is there a step by step guide to splitting up my tempdb file to have IO for my xeon quadcore?
0
Comment
Question by:FTGE
  • 2
3 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 26110116
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26110117
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
 
LVL 5

Assisted Solution

by:rk_india1
rk_india1 earned 1000 total points
ID: 26124667
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question