how to ensure the data is evenly among multiple data files

Question

how to ensure the data is evenly among multiple data files

dbdmora

Say Hey Kid

Points: 684
More actions
March 26, 2012 at 6:43 am

#266186

Hello, We have a database server that came from a 3 data file server to a 6 data file server. What I mean is we had A.MDF, A1.NDF, A2.NDF and now it is A.MDF, A1.NDF, A2.NDF, A3.NDF, A4.NDF and A5.NDF
A.MDF-A3.NDF has most of the data files at 60% Used disk but the rest of the NDF files(A4.DNF-A5.NDF) less than 15% used.
What is the best way to distribute the data more evenly among all the Disks(data files)? I feel I might have better performance if all 6 disks were used equally instead of just 3 disks doing all the work.
Thanks for your help.

Viewing 10 posts - 1 through 9 (of 9 total)

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

calvo SSChampion Points: 12930 More actions · Answer 1

If the data files are all in the same file group, there's nothing you can do. The engine handles data file fill rates and all that good stuff.

Check this link out for data fill information

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If all the files are in the same filegroup, the data will be evenly distributed across them due to the proportional fill algorithm SQL uses.

Performance from multiple filegroups is a lot, lot more complex than just multiple filegroups, multiple disks. Are you seeing IO bottlenecks on some of the files?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dbdmora Say Hey Kid Points: 684 More actions · Answer 3

Hello Calvo, what if I restrict the growth for the 3 files, will it start filling the rest of the files? Do you think this will work?

calvo SSChampion Points: 12930 More actions · Answer 4

Restricting growth of a file is not the same as data fill. As Ms. Shaw asked, are you experiencing bottlenecks with IO on a particular disk?

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Before you start fiddling with files and settings... What is your goal here? What are you trying to achieve?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dbdmora Say Hey Kid Points: 684 More actions · Answer 6

Thanks for the replies.

We are not currently having an I/O issues. I just want to ensure having the data in the files not equal in size will not hinder performance in the future. My goal is to ensure what we have done in the beginning is the correct way or should we have done it a different way when we moved the database.

I did run SQL Doctor from Idera and It had this as a critical warning, is this legit?

Finding: tempdb data files are not all the same size
SQL Server will allocate pages for objects from the various tempdb data files in proportion to their relative sizes. The larger tempdb data files will experience more I/O stress than the smaller files, which will decrease overall performance. The largest file is 990.8 MB and the smallest file is 97.2 MB.
Recommendation:
It is recommended that all tempdb data files should be sized exactly the same, and if autogrowth is allowed, then the autogrowth increment should also be equalized across all data files.
However, if autogrow is turned on for these data files, then they may still grow unevenly regardless of the autogrowth increment. This occurs when a new object allocation necessitates growing some but not all of the data files to find sufficient storage space.

On another note, we are getting new Disk storage for another server. We will be going from a 3 data file server to a 6 data file server. our last approach was creating the new NDF files and have them slowly grow or should we initially start them all equal? Let me know if this is another topic to create or we can discuss it here.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

dbdmora (3/26/2012)
Thanks for the replies.
We are not currently having an I/O issues. I just want to ensure having the data in the files not equal in size will not hinder performance in the future.

Uneven file data splits is not going to hinder performance by itself. Don't spend time and effort on something that's unimportant and of no impact.

I did run SQL Doctor from Idera and It had this as a critical warning, is this legit?

Yes, that is legit, please note it is for TempDB ONLY.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dbdmora Say Hey Kid Points: 684 More actions · Answer 8

Gila, what is the best practice to get TempDB equally the same size? Thanks for your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

It's telling you that all the files must be the same size, so the sizes set by alter database or in the file dialog should be the same for all the data files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass