How files grow within a file group ?

In this blog I will explain how data allocation happens in a file group that has mulitple files under it. Create a database FileG with 2 files under the primary file group. As can be seen clearly I have assigned both the data files in PRIMARY file group the same size (4 MB).

USE master;

GO

CREATE DATABASE FileG

ON PRIMARY

( NAME='FileG_P',

FILENAME='D:\FileG_P.mdf',

SIZE=4MB,

FILEGROWTH=1MB),

( NAME='FileG_S',

FILENAME='D:\FileG_S.mdf',

SIZE=4MB,

FILEGROWTH=1MB)

LOG ON

( NAME='FileG_Log',

FILENAME=

'D:\FileG_Log.ldf',

SIZE=1MB,

FILEGROWTH=1MB);

GO

-- I will now create a table with 2 columns and make sure that each row of the table occupies 1 page

USE FileG

GO

CREATE TABLE Test(c1 int identity, c2 char(8000));

-- Currently there are no pages allocated to this table. This can be verified using the DBCC IND command which returns 0 rows

DBCC IND(FileG, Test, 1)

-- Lets insert 32 rows in the table Test, which will allocate roughly 32 pages + 2 IAM pages

DECLARE @i int= 1;

WHILE (@i <= 32)

BEGIN

INSERT INTO Test VALUES ('a');

SET @i = @i + 1;

END



Execute the DBCC IND again. Clearly it shows that equal number of pages have been allocated from both the files. It looks like the pages have been allocated propotionally. Since both the files were of the same size, same number of data pages (16) were allocated from each file.



DBCC IND(FileG, Test, 1)



To confirm it is propotional, lets now increase the size of one of the files in the database to do that execute the following script to drop and recreate this database. Here I have kept the size of the second file as 8MB, double the size of the first file (4MB)

USE master;

GO

DROP DATABASE FileG;

GO

CREATE DATABASE FileG

ON PRIMARY

( NAME='FileG_P',

FILENAME='D:\FileG_P.mdf',

SIZE=4MB,

FILEGROWTH=1MB),

( NAME='FileG_S',

FILENAME='D:\FileG_S.mdf',

SIZE=8MB,

FILEGROWTH=1MB)

LOG ON

( NAME='FileG_Log',

FILENAME=

'D:\FileG_Log.ldf',

SIZE=1MB,

FILEGROWTH=1MB);

GO

-- I will recreate the same table again

USE FileG

GO

CREATE TABLE Test(c1 int identity, c2 char(8000));

-- Populate 24 rows in the table, which will allocate roughly 24 pages + 2 IAM pages

DECLARE @i int= 1;

WHILE (@i <= 24)

BEGIN

INSERT INTO Test VALUES ('a');

SET @i = @i + 1;

END

Execute the DBCC IND again. The picture is pretty much clear. File 1 (PageFID = 1) has 8 data pages where as File 3 (PageFID = 3) has 16 data pages. The ratio is 1:2 which is the same as the ratio of size of the data file (4 MB/8 MB = 1:2). Thus the data gets distributed propotionally across the data files. As in our case it is 1 extent : 2 extents

DBCC IND(FileG, Test, 1)