number of datafiles question

Question

number of datafiles question

ruben luengas

Newbie

Points: 3
More actions
April 7, 2005 at 2:43 am

#362323

Hello, I have to create a new crm database. In the beginning it will have 200 Gb of data and in the future 400 Gb.
Does anybody knows a rule or indication to know how big should be each datafile or how many datafiles I should create.

REgards in advance.
Ruben

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kishore-132325 Hall of Fame Points: 3784 More actions · Answer 1

Well it all depends on how your enviornment is setup, what hardware configuration you have, what RAID levels you are using, what kind of indexes have you defined on your tables.

Are you copying the files from any other sql server. How is your size 200GB in the begining ?

--Kishore

ruben luengas Newbie Points: 3 More actions · Answer 2

200 Gb will be stored in a SAN. Now, because a bad administration it has 200gb in a single datafile. New server will have 4 Xeon proc. The indexes are not very good in this datamodel.

regards

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

multiple data files only work ( from a performance point of view ) if each exists on a seperate disk or array. Generally multiple datafiles on the same disk/array will be counterproductive.

On the plus side datafiles allow for partial database backups.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

ruben luengas Newbie Points: 3 More actions · Answer 4

colin, I think that if you have 4 proc you can open 4 threads to read 4 datafiles at the same time. ¿is this correct?

I am fear of getting 200gb datafiles in my sql server.

any opinions?

regards

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

A good SAN implementation would utilize not LUNS (A slice of space across an array of disks), but META-LUNS (a slice space across many arrays of disk). Also, as much buffer memory on the SAN as possibly (8+ Gb is a minimum). The fiber channel speeds of an architecture like this are probablymore than sufficient. Add to this 4 Xeon CPUs (functions like 8 with hyper-threading) you've got lots of horses. I concentrate more on the SAN than the CPU in this case.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."