[read this post on Mr. Fox SQL blog]

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!

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.

USE [master]
GO
CREATE DATABASE [PropFillDB]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'PropFillDB', FILENAME = N'C:\SQLData\PropFillDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [SECONDARY] DEFAULT
( NAME = N'PropFill_File01', FILENAME = N'C:\SQLData\PropFill_File01.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'PropFill_File02', FILENAME = N'C:\SQLData\PropFill_File02.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'PropFill_File03', FILENAME = N'C:\SQLData\PropFill_File03.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'PropFill_File04', FILENAME = N'C:\SQLData\PropFill_File04.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'PropFill_File05', FILENAME = N'C:\SQLData\PropFill_File05.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'PropFillDB_log', FILENAME = N'C:\SQLData\PropFillDB_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 COLLATE Latin1_General_CI_AS
GO
ALTER DATABASE [PropFillDB] SET RECOVERY SIMPLE 
GO

Now create a blank table specifically on that SECONDARY filegroup.  For simplicity the table fills up most of the 8K page.

USE [PropFillDB]
GO
CREATE TABLE dbo.ProportionalFill
(
 MyID INTEGER NOT NULL,
 MyFullPageFiller CHAR(8000) NOT NULL
)
ON [SECONDARY]
GO

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!)

INSERT INTO dbo.ProportionalFill (MyID, MyFullPageFiller) SELECT 1, 'PROPORTIONAL FILL'
GO 1280

Just to allay any questions – Is this behavior just a function of a single table in the filegroup?

Well, lets create a 2nd table in the same SECONDARY filegroup and repeat the data load but increase it by x10 (because we can!).

CREATE TABLE dbo.ProportionalFill2
(
 MyID INTEGER NOT NULL,
 MyFullPageFiller CHAR(8000) NOT NULL
)
ON [SECONDARY]
GO
INSERT INTO dbo.ProportionalFill2 (MyID, MyFullPageFiller) SELECT 1, 'PROPORTIONAL FILL'
GO 12800

As can be seen the distribution of data is rather even – no data bulking in any specific file – no trace flags needed for the distribution – just vanilla flavor out of the box SQL in action!

Adding New Data Files, Revisit Proportional Fill

But oh, lets not leave it there!

That’s not exactly how proportional fill is really meant to work.

What does SQL do if you add say 5x new empty data files to the same SECONDARY filegroup?

(Q) Will SQL dynamically redistribute?  Well – I can tell you NO it will not!

(Q) Will SQL favor the empty files (with free space) more than the full files?  Mmmm…

Lets test it!  Lets add 5x more new data files to the SECONDARY filegroup.

USE [master]
GO
ALTER DATABASE [PropFillDB] 
ADD FILE ( NAME = N'PropFill_File06', FILENAME = N'C:\SQLData\PropFill_File06.ndf' , SIZE = 22528KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO
ALTER DATABASE [PropFillDB] 
ADD FILE ( NAME = N'PropFill_File07', FILENAME = N'C:\SQLData\PropFill_File07.ndf' , SIZE = 22528KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO
ALTER DATABASE [PropFillDB] 
ADD FILE ( NAME = N'PropFill_File08', FILENAME = N'C:\SQLData\PropFill_File08.ndf' , SIZE = 22528KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO
ALTER DATABASE [PropFillDB] 
ADD FILE ( NAME = N'PropFill_File09', FILENAME = N'C:\SQLData\PropFill_File09.ndf' , SIZE = 22528KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO
ALTER DATABASE [PropFillDB] 
ADD FILE ( NAME = N'PropFill_File10', FILENAME = N'C:\SQLData\PropFill_File10.ndf' , SIZE = 22528KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO
EXEC dbo.sp_helpdb 'PropFillDB'
GO

Now lets load some data again and see if the first 5 files stay about the same and the second 5 files get more of the data.

INSERT INTO dbo.ProportionalFill (MyID, MyFullPageFiller) SELECT 1, 'PROPORTIONAL FILL'
GO 12800

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