SQL Filegroup/File Proportional Fill Algorithm

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.

https://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

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!

Continue reading

Advertisements