Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com