SQL Server 2008 File Write Behaviors

Question

Sort of a 101 question: with multiple files in a user defined filegroup, what is SQL Servers behavior when inserting rows? Round robin? Write until full and move on to the next? I have found some interesting posts regarding tempDb on this subject that suggest round robin, but nothing conclusive.

Answer 1

SQL Server uses a proportional fill agorithm to write data to a filegroup with multiple files. That is to say, if your filegroup contains two files, and the first has twice as much free space as the second, the first will receive (roughly) twice as many writes. SQL Server recalculates this ratio on a regular basis, as free file space will of course change with time.

Here's a very good discussion on the subject:

http://groups.google.ca/group/microsoft.public.sqlserver.server/browse_thread/thread/a77db2ded38c58e9

Answer 2

Thanks - exactly the info I was seeking.