SQL Server Performance Forum – Threads Archive
Large DB SQL70Hello.
I have a large DB (230 GB) and I need to distribute the data files over 4 disks.
Disk 0 will contain the Transaction Logs and disks 1 to 3 will contain the data files. The data files will have fixed sized.
The question is: how can I to determine how many data files I need, and the size of the data files? JCF
Will the data disks (1-3) be in a RAID partition, such as RAID 5 or RAID 10? If so, spreading out the data files is not really necessary, although some people do it just to eek out a little more performance, although I am not sure it is worth the effort. ——————
Brad M. McGehee
I agree with Brad. Often the effort is better spent analysing and improving the architecture of the database rather than its physical layout. Of course, if youre not using RAID, id say its a whole different ball game.. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
OK. I’ll explain more details:
every disk 0 to 4 is a RAID system (into a Compaq Storage System). Every RAID system (or "disks") is conected by one fiber channel board. This is why I have 4 "disks" of 100 Gb.
Then, I think that to make one file fo 80 Gb in every disk, can be heavy to the Operating System.
I have never seem any documented benefits to having more than 1 file on a disk. SQL Server will not generate more parallel I/Os just because there is more than one file on a disk drive. Also I’ve never heard of an 80 GB file being a problem with the OS. Could you tell us why you think it would be a problem? Perhaps I’m missing something…
Now that I have a clearer picture, I suggest dividing the files as equally as possible over each of the available arrays, limiting each array to a single file, as suggested by rortloff. Also, you may want to consider using one of the drives for all of the log files, and using the rest to distributes the database files evenly as possible. Another alternative, if it is feasable in your situation, is to configure all of these drives into a single RAID 10 array. This will give you even better, overall performance. ——————
Brad M. McGehee