How SQL Server fills mdfs and ldf with data: Proportional fill and Round robin

Hi Guys,

Welcome back!

Last time we talked about the TempDB database Here.

We ran some benchmarks to inspect performances variation adding one or more mdf files to the TempDB.

So we have seen in which order mdf and ldf files are filled.

You surely noted that we didn't say anything about how they are filled.

Well we will do it now!

So my friends...

Enjoy reading this post i wrote (from my mobile office overlooking the Alps!!!)

Simple case, one mdf file

For this first simple test we create a new database named TestPF.

Our dabatase will have a single mdf file.

DROP DATABASE [TestPF]

 
CREATE DATABASE [TestPF]

ON

(NAME = N'PF', FILENAME = N'E:\PF.mdf', SIZE = 131072KB, FILEGROWTH = 131072KB)

LOG ON

(NAME = N'PF_log', FILENAME = N'E:\PF_log.ldf', SIZE = 131072KB, FILEGROWTH = 131072KB)

GO
 

ALTER DATABASE [TestPF] SET RECOVERY SIMPLE

GO

Now we define a Query that return how much our mdf and ldf files are full:

       

USE [TestPF]

 

SELECT 1 ID, (

SELECT 

   CASE WHEN f.fileid = 1

   THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 1

) AS [File1 Space Used in MB], (

SELECT

   CASE WHEN f.fileid = 2

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 2

) AS [File2 Space Used in MB], (

SELECT

   CASE WHEN f.fileid = 1

   THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/(1.0*size) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 1

) AS [File1 Space Used in %], (

SELECT

   CASE WHEN f.fileid = 2

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1.0*size)  AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 2

) AS [File2 Space Used in %]


GO

Third step: we define a stored procedure named Write_2gb.
When executed this sp will write 2 gb of data into a table named Test.


Create Table Test (id int identity(1,1), testo char(117))

       

CREATE PROCEDURE Write_2gb AS

BEGIN

       -- scrivo 2GB di dati

       DECLARE @i INT = 0;

       WHILE @i < 16*8*2

       BEGIN
 

         INSERT Test

         (

         testo

         )

         -- scrivo 128 byte x 65535 -> 8 MB

         SELECT TOP ( 65535 ) c1.NAME FROM sys.columns c1 join sys.columns c2 on c1.column_id = C1.column_id

 
         SET @i = @i + 1

       END
END

Fourth step, now define T-SQL that each second print to video how much our mdf and ldf are full.

This procedure must be executed while the SP Write_2GB is in execution opening another tab of the SSMS:

       

DECLARE @i INT = 0
 

WHILE @i < 240

BEGIN

 

   WAITFOR DELAY '00:00:01'
  

   INSERT INTO ##temp
 

       SELECT 1 ID, (

       SELECT 

          CASE WHEN f.fileid = 1

          THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

          ELSE 0 END

       FROM sysfiles f

       WHERE f.fileid = 1

       ) AS [File1 Space Used in MB], (

       SELECT

          CASE WHEN f.fileid = 2

          THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

          ELSE 0 END

       FROM sysfiles f

       WHERE f.fileid = 2

       ) AS [File2 Space Used in MB], (

       SELECT

          CASE WHEN f.fileid = 1

          THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/size AS DECIMAL(10,2))

          ELSE 0 END

       FROM sysfiles f

       WHERE f.fileid = 1

       ) AS [File1 Space Used in %], (

       SELECT

          CASE WHEN f.fileid = 2

          THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/size  AS DECIMAL(10,2))

          ELSE 0 END

       FROM sysfiles f

       WHERE f.fileid = 2

       ) AS [File2 Space Used in %]
 

   SET @i += 1
 

END

OK areready for the test?
So execute at the same time both the stored procedure write_2gb and the T-SQL just seen!

This is the result:

Monitoring also the datababse_file_size_change event:

Things became more interesting when you have more than one mdf file!

Proportional fill and Round robin

In order to allocate extents from files SQL Server have uses two algorithms called proportional fill and round robin algorithm.

The round robin algorithm consists in allocating in succession each file of a filegroup.
This is what we learned in the prevous post here.

The storage engine of SQL Server however consider how much space in each mdf or ldf file is free.

So, the storage engine allocate proportionally more extents in the file with more free space.

This is called proportional fill.

But how exactly Proportional fill works?

Each file in a filegroup is assigned a number called skip target.

The higher skip target value is 1.

Skip target values are evaluated during the round robin procedure.

If the skip target value is equal to 1 then an allocation take place otherwise if the value is greater than 1 then no allocation take place and the values id decremented by 1.

Now! How to calculate the skip target?

For each file the skip target is the integer result of free entents in file with most free space / number of free extents in this file.

doing so we have that:

1) files in the filegroup with the least amount of free space will have the highest skip targets

2) there has to be at least one file in the filegroup with a skip target of 1.

Pratically:

If file f1.mdf has 100 MB free and file f2.mdf has 200 MB free,
One extent is allocated from file f1,
Two extents are allocated from file f2

In this way, both files become full at about the same time.

We will now see more clearly through an example!

The Examples

First of all enable the traceflag 1165 and clear the log.

       

DBCC TRACEON (1165, 3605);

GO

EXEC sp_cycle_errorlog;

GO

USE [master];

GO

Now create a new databases TESTPF

       

CREATE DATABASE [TestPF] ON PRIMARY (

    NAME = N'PF',

    FILENAME = N'E:\PF.mdf',

    SIZE = 131072KB,

    FILEGROWTH = 131072KB)

LOG ON (

    NAME = N'PF_log',

    FILENAME = N'E:\PF_log.ldf',

       SIZE = 131072KB,

    FILEGROWTH = 131072KB

);

Now Watch to the log with the command below.



EXEC xp_readerrorlog;

GO

the PF have 2003 extends and a skip target equal to 1.

Now we will add a second mdf file:

       

ALTER DATABASE [TestPF] ADD FILE (

    NAME = N'PF2',

    FILENAME = N'E:\PF2.ndf',

    SIZE = 131072KB,

    FILEGROWTH = 131072KB);

GO

       

EXEC xp_readerrorlog;

GO

Note that the two data files have not the same number of extents but the integer result of 2048 / 1997 is equal to 1 and then each file have a skip target equal to 1.

Finally add a third data file but with a bigger size:

       

ALTER DATABASE [TestPF] ADD FILE (

    NAME = N'PF3',

    FILENAME = N'E:\PF3.ndf',

    SIZE = 262144KB,

    FILEGROWTH = 262144KB);

GO

       

EXEC xp_readerrorlog;

GO

And now?

What is happened?

Do again the calculation....

FP3 file have the most free space and so it’s skip target value is 1.
FP1 file have this skip target: 4096/2006 = 2
PF2 file have this skip target: 4096/2049 = 2

Now run again our test!

The T-SQL below give us the space used into the mdfs..

       

SELECT 1 ID, (

SELECT 

   CASE WHEN f.fileid = 1

   THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 1

) AS [FP Space Used in MB], (

SELECT

   CASE WHEN f.fileid = 3

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 3

) AS [FP2 Space Used in MB],

(

SELECT

   CASE WHEN f.fileid = 4

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 4

) AS [PF3 Space Used in MB],

(

SELECT

   CASE WHEN f.fileid = 2

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1024.0/8.0) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 2

) AS [PF_Log Space Used in MB],

(

SELECT

   CASE WHEN f.fileid = 1

   THEN CAST(FILEPROPERTY(f.name, 'SpaceUsed')/(1.0*size) AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 1

) AS [FP1 Space Used in %], (

SELECT

   CASE WHEN f.fileid = 3

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1.0*size)  AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 3

) AS [PF2 Space Used in %], (

SELECT

   CASE WHEN f.fileid = 4

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1.0*size)  AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 4

) AS [PF3 Space Used in %], (

SELECT

   CASE WHEN f.fileid = 1

   THEN CAST(FILEPROPERTY(name, 'SpaceUsed')/(1.0*size)  AS DECIMAL(10,2))

   ELSE 0 END

FROM sysfiles f

WHERE f.fileid = 1

) AS [PF_Log Space Used in %]

INTO ##temp

GO

The situation before loading the data

The situation after loading the data

How mdf are filled?

Graphically:

A look at the data returned from the File Size chance Event:

A look to the error log:

Going deep: spinlock contention and performances.

Skip targets are protected by a spinlock named FGCB_PRP_FILL.

So, in order to determine which file to allocate this spinlock must be acquired for each extent allocation

There is an unique exception, when files have all the skip value equal to 1.

In that case there isn't any reason to acquire a spinlock!

However, with modern discs you won't notice any difference.

Let's do one last test anyway.

AND NOW THE LATEST TEST: 3 MDF WITH EQUAL SIZE

       

ALTER DATABASE [TestPF] ADD FILE (

    NAME = N'PF3',

    FILENAME = N'E:\PF3.ndf',

    SIZE = 131072KB, --262144KB,

    FILEGROWTH = 131072KB); --262144KB);

GO