SQLMI General Purpose Database: How to distribute data between 2 data files

Question

Hi ,
I am testing approaches for balancing data amongs the datafiles of the DB. We have a SQL MI GP data base. there was only one datafile for the db.

There is only one table in the db, and it has a clustered index . There are close to 800,000 rows on the table.

we added one more data file i.e a secondary data file .
Both files are sized the same with identical growth increment values and both files are on the Primary file group.

And to balance the data between the 2 data files, we attempted the following:

1) Rebuild the Clustered Index ci of the tables:
This shows only little data movement.

2) Added a new filegroup and moved the clusteredindex to the new fg and then transsferred it back into the Primary file group.
This shows better movement than 1) but nowhere close to equal .

If i run dbcc showfilestats , the mdf file has around 5000 used extents and the secondary data file has around 300 used extents.

this is nowhere close to even.

Please can anyone suggest what might be going on here? or is there no definite way to redistribute data evenly between the data files of a sql db?

thanks

Answer 1

Hi @Biju Mathew Thank you for posting your question.
This is a common question addressed the same way as for the SQL Server. SQL Server does not automatically rebalance data across files immediately after a new file has been added to the filegroup. Once you add a new file, it is empty at first, and lots of data writes needs to happen in time for SQL Server to naturally rebalance this in time. This article addresses this question well, there are many more articles in SQL Server literature that you can look up.

Hope this helps.

Regards,
Oury