question

MSProg-4478 avatar image
0 Votes"
MSProg-4478 asked OuryBa-MSFT commented

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

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









azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MSProg-4478 Hope you had the chance to check the above answer. Please feel free to reach out if you have additional queries.

Regards,
Oury

0 Votes 0 ·

1 Answer

OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered

Hi @MSProg-4478 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.