T-SQL Tuesday #40 - Proportional Fill within a Filegroup

T-SQL Tuesday #40 is underway, and this month's host is Jennifer McCown (blog|twitter).  The topic is about File and Filegroup Wisdom.  Jennifer says she's a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup.  This should be pretty common knowledge, but I still talk to a lot of DBAs that don't know anything  about it, or if they have heard of it, they still don't know how it works.

The proportional fill algorithm is used to keep the amount of free space within a filegroup evenly distributed across all files in a filegroup.  SQL Server's proportional fill falls in line with the strategy of placing your files and filegroups across multiple disks, and thus, allowing for improved I/O performance.

Let's say we need to add more storage space for our AdventureWorks2012 database that has outgrown the current drive D.  Because of storage limitations, we can't add any more space to D, so our only choice is to add a completely new drive E.  

Once we add the new E drive to the server, we add a new data file to the PRIMARY filegroup of the AdventureWorks2012 database using the following query.

USE master;

GO

ALTER DATABASE AdventureWorks2012

ADD FILE (

     NAME = N'AdventureWorks2012_Data2'

    ,FILENAME = N'E:\MSSQL11.TEST1\MSSQL\DATA\AdventureWorks2012_Data2.ndf'

    ,SIZE = 200MB

    ,FILEGROWTH = 1024KB

) TO FILEGROUP [PRIMARY];

GO

One might think we're safe at this point; however, because of the proportional fill feature we're not.  Once new data is written to the data files, SQL Server will create the new page allocations on the newly created AdventureWorks2012_Data2.ndf file because it has a higher percentage of free space compared to AdventureWorks2012_Data.mdf.  Drive E now suddenly becomes a new I/O hotspot on the server.

You can check the space used with the following query.

USE AdventureWorks2012;

GO

SELECT

     name AS 'LogicalName'

    ,physical_name AS 'PhysicalName'

    ,CONVERT(INT,ROUND(size/128,0)) AS 'Size (MB)'

    ,CONVERT(INT,ROUND(FILEPROPERTY(name,'SpaceUsed')/128,0)) AS 'SpaceUsed (MB)'

FROM sys.database_files

WHERE type = 0;

GO

To avoid this disk hotspot issue, we need to have the data more evenly balanced across both files in the filegroup in terms of data page allocations.  The quickest way to do this is to rebuild all of the clustered indexes within the database.

ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REBUILD;

ALTER INDEX [PK_ErrorLog_ErrorLogID] ON [dbo].[ErrorLog] REBUILD;

ALTER INDEX [PK_Department_DepartmentID] ON [HumanResources].[Department] REBUILD;

:

:

ALTER INDEX [PK_Store_BusinessEntityID] ON [Sales].[Store] REBUILD;

GO

SQL Server will do its best to automatically rebalance all of the page allocations across all files within the same filegroup.  In our case, both data files are still part of the PRIMARY filegroup. 

Check the space used again with the following query.

USE AdventureWorks2012;

GO

SELECT

     name AS 'LogicalName'

    ,physical_name AS 'PhysicalName'

    ,CONVERT(INT,ROUND(size/128,0)) AS 'Size (MB)'

    ,CONVERT(INT,ROUND(FILEPROPERTY(name,'SpaceUsed')/128,0)) AS 'SpaceUsed (MB)'

FROM sys.database_files

WHERE type = 0;

GO

Now what we have is much more evenly balanced allocation across both data files.  This will allow SQL Server to even distribute the write I/O across both disk drives.

By doing this one index maintenance step after adding a new file, you'll help prevent a write hotspot on one of your disks and help SQL Server improve its I/O performance.  But keep in mind that proportional fill only affects all files in the same filegroup.  If we had added the second file to a new filegroup, then we would have to manually move tables to the new filegroup.

For more info on files and filegroups, check out BooksOnline.