Round Robin, Proportional Fill and Performance Considerations

Round Robin, Proportional Fill and Performance Considerations

Introduction

This is my first blog post so I hope you enjoy reading it.  I decided on this topic after discovering some unexpected SQL Server behavior whilst performance tuning a VLDB at work.  There also seems to be a misconception that Round Robin and Proportional Fill are the same thing.  The post focuses primarily on how SQL Server behaves when multiple data files within a filegroup are used.  This includes when multiple data files are added to user databases and tempdb.  The files can be either be in the PRIMARY or a user defined filegroup and can exist either on the same disk or others.  If you are chasing IO performance you are most likely hosting files over multiple disks.  We will also delve into how you can control the behaviour of data allocations which can potentially lead to performance improvements.  Please note this post is targeted towards high IO systems, the larger the system the higher the potential benefits.  If the systems you are supporting are loading under 1GB an hour the benefits may not be as visible as opposed to someone loading 10GB per hour.