How are data distributed on multiple files

Question

How are data distributed on multiple files

jens.riis

SSC Rookie

Points: 49
More actions
March 12, 2009 at 3:21 pm

#219053

I have Database with 4 * 50 GB data files which is almost full.
The questions is which approach is best:
Add another 50 GB data file.
Increase the size of the existing files.
The database was created with 4 * 50 GB data files .
They are set to autogrow for security reasons, but I would prefer not to let them grow this way.
I know that a large portion of the space is consumed by one table.
It's a SQL 2005 Standard Ed. SP 2, 64 bit - 8 cores. 32 GB RAM
(upgrading to SP3 soon)
The data files all resides in primary filegroup on a HP SAN - raid 1.
(as far as I understand this SAN spead files across many spindles)
If I add another 50 GB file will SQL server use all 5 datafiles evenly ?
Somehow I like the idea of more smaller files - easier to handle.
What are the performance issues ?
Is there any whitepapers on this issue ?

Viewing 4 posts - 1 through 3 (of 3 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

for multiple files in a filegroup SQL adds data in a round robin fashion using proportional fill algorithms, i.e how much data is added to each file depends on how much free space there is, so that way SQL tries to keep each file filled the same and spread the growth of each file evenly.

There is no performance benefit in adding a fifth file to the filegroup and it just further complicated the database structure, I would manually grow each existing file by the same amount to keep usage of each file the same.

---------------------------------------------------------------------

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

But you can gain performance gain from adding another file if you put it on different spindle on your San :).

If it is not available growing the Files would be the simpler option :).

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

jens.riis SSC Rookie Points: 49 More actions · Answer 3

Thanks to both of you

I think I will just grow the existing files with a significant increase - at least for this time