TempDB CPU
-
I read an article about having TEMPDB file group for each CPU..
We have 4 cpu on some servers but only one TEMPDB Data and Log is it recommended for me to add another TEMPDB ...
If any one has done that i like to see how to do this...Do you just add another filegroup.?
tempdb supports only one data filegroup and one log filegroup. By default, the number of files is set to 1. Multiple files can be created for each filegroup. Adding more data files may help to solve potential performance problems that are due to I/O operations. Increasing the number of files helps to avoid a latch contention on allocation pages (manifested as a UP-latch). The recommended solution is to set the number of files to match the number of CPUs that are configured for the instance. This is only a recommendation—the number of files might not be the same as the number of CPUs.
Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal. Generally, for SQL Server 2005 it is recommended that you create tempdb files striped across fast disks.
-
Sounds like an interesting article... do you have the link? I'd like to take a peek...
Thanks, Tracey.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
http://support.microsoft.com/default.aspx/kb/834846
The ppt above is very usefull to understand, based on your app transactions behaviour, whether or not your tempdb would need as many datafiles into the default filegroup as the number of CPUs on the sql machine.
However, tempdb should be stored on its own physical drive.
-
Very very very important - NOT filegroups, files. For any database, not just tempdb, you may gain performance by having the same number of files per filegroup as you have cpu sockets or cores ( don't do this for hyperthreading ).
Putting tempdb on it's own array is another matter and not directly related to database files.
here's an example to create an 8 file tempdb
use
master
go
Alter
database tempdb modify file (name = tempdev, filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db1.mdf',size = 60Mb,filegrowth = 20Mb)
use
master
go
Alter
database tempdb add file (name = tempdev2, filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db2.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev3, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db3.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev4, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db4.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev5, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db5.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev6, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db6.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev7, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db7.mdf',size = 60Mb,filegrowth = 20Mb),
(
name = tempdev8, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db8.mdf',size = 60Mb,filegrowth = 20Mb)
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for replying.
Right now our TEMPDB is on raid5 along with all the operating system on first 5 disks of our SAN.
We moved some of the other database to RAID 1 then i read about the additional TEMPDB for CPU.
If i was to add another database mdf
Alter database tempdb add file (name = tempdev2, filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db2.mdf',size = 60Mb,filegrowth = 20Mb),
(
Could i add this to the RAID 1. Leaving orginal tempdb there on raid 5
Very very very important - NOT filegroups, files.??...could you explain here what you mean. I think you mean create MDF not filegroup...
-
Files are objects that reside in FileGroups (think of it like individual sheets of paper in a manilla folder. The folder is the file group and the sheets of paper are the individual files). Every database has a Primary filegroup and most can have Secondary filegroups. However, only one FileGroup at a time can "collect" data. So if your Secondary filegroup is set as the default group, it will be collecting data and if the Primary (as is usual) is set as the default group, it will be collecting the data.
The idea of splitting the load between files works well when you have multiple files in the Primary group, with each file set on a different hard drive. This way, information gets written in a striping method between the various files and it takes advantage of multiple disk controllers to improve the speed of writing to the disks.
If you can split the TempDB files between multiple disks and still keep the TempDB files on drives that do NOT house the other system or user databases, then you can really spice up performance. But not that many people have that much money to spend on the necessary hardware.
Look up Files and FileGroups in Books Online. This reference will give you more details on the difference between the two if you still don't understand it.
-
Sure would be nice if you posted the URL of the article you read...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
We were using 1 tempdb. After reading that article linked above and the section on the tempdb in the book "Inside Microsoft SQL Server 2005: the storage engine" I went and broke it up into 4 files for our server.
We're running 2 dual core xeons and I would say there was a small bump in performance. Nothing amazing but then again the tempdb is sharing the same array as the main database so we're getting a big hit there. Once I can get the boss to pony up and buy another array for the server we'll be rocking
-
Can you post what you did on your tempdb when you say added 4 files .
I have TEMPDB
Under Data Files
File Name
TempDev M:\
Under Transaction Log
File Name
TempLog O:\
FILEGROUP Primary
M and O are raid 5 so i would like to add the files put these to point to R which is our RAID 1 file
Bear with me first time doing this
-
Can you post what you did on your tempdb when you say added 4 files .
I have TEMPDB
Under Data Files
File Name
TempDev M:\
Under Transaction Log
File Name
TempLog O:\
FILEGROUP Primary
M and O are raid 5 so i would like to add the files put these to point to R which is our RAID 1 file
Bear with me first time doing this
-
If you have a screen shot of the files in tempdb that be nice
-
Tracey,
Look back at the code Colin posted. Just change the FileName stuff (where it currently points to the D: drive) to your R: drive, path and new filename. You'll have to write that entire line of code over for each single file you add. Or you can add them through SSMS by right clicking the database in question and going to Properties -> Files.
-
Thanks, Edogg... appreciate the effort...
... but why in the hell won't the OP tell me
All take, no give... I've asked Tracey twice, now. --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's life in the forums
* Noel
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply