I had a question at work recently where there was some confusion around how SQL Server allocates data across data files within a filegroup in a user database. There was a mention that data was not being distributed evenly across files and also that a trace flag was needed for SQL Server to distribute data evenly. I am uncertain if those circumstances were database config related or something else outside of proportional fill.

So I thought I’d do a quick post just to clarify how proportional fill works via demonstration.

SQL Server has used a proportional fill strategy across data files in a filegroup for some time (as long as I care to remember anyway) and this has been pretty well documented in SQL BoL and a number of blog posts on the web already.

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file.

As soon as all the files in a filegroup are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically.

When multiple files are involved, and if these are ideally located on different physical spindles on the underlying disk subsystem, then a rather nicely performing data striping can be achieved for the database. If proportional fill kicks in and starts to focus on files with more free space then you may get hot spots for those files. However nowadays with auto-tiering SAN’s, SSD and (abstracted) cloud storage (for IaaS deployments) this is beginning to matter less and less.

However – Lets get into breaking down the proportional fill algorithms!

Lets go ahead and test this puppy out!

Setup the Test Database

First create a blank database that has 5 secondary files of 1MB each allocated to a single filegroup called SECONDARY. All files are the same small size.

Lets see what this database looks like. Note that all 5 files are exactly 1MB each.

Load Some Data and Check Proportional Fill

Now lets put in some data and then see how the data was distributed across the files. If proportional fill is working we should see a reasonably even distribution of 1MB growth increments across all 5 files as there’s no free space to content with. (which is of course exactly what we see!)

As can be seen above the data HAS been weighted by SQL Server into the empty files – in fact data continues to be distributed across all files with the first 5 files being perfectly balanced and the second 5 files in “catchup mode”.

Continuing to load data will result in the later 5 files becoming full before once again SQL will distribute data in a “round robin” fashion again across all files just like we saw in demo at the start of the blog.

So in summary the “fullness” of a files DOES factor into the decision of SQL Server to place data in any specific file.

So there you have it – the data does not lie!

AND of course, as I always say, please test this yourself as your results may vary!

NOTE – In the interest of versions the test was performed on SQL Server 2014 Enterprise (12.0.4100.1)

Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here

Rolf Tesmer works as an Azure Data Solution Architect (DSA) in Australia for Microsoft.
Rolf has an MCSE in Data Management & Analytics, an MCSE in Data Platform and an MCSE in Business Intelligence (BI).
Rolf has been working with the SQL data platform since v6.0 (that’s 1994!) and has done just about everything you can around data related platforms, solutions and architectures ever since then and has scoped, designed and delivered 100’s of data solutions in that time.
Rolf has had the opportunity to present extensively at Ignite, PASS, TechEd, SQL Saturday, SQL User Groups, MeetUp’s and Seminars, Roadshows, etc and really enjoys sharing and learning new ideas.