there is something called the proportional fill algorithm in sql server, and it is described here by Paul Randal.
from that article the main point is:
Round robin means that the SE will try to allocate from each file in a
filegroup in succession.
the question is:
why would it not be possible in a single data file?
it reminds me of RAID - raid 0 is striping. you need at least 2 disks to implement it on.
then it will spread the data in these two or more files and read it quickly.
the robin algorithm can only be implemented in two or more files in the filegroup so that it can take advantage of these.
in the same webpage above the author shows an example (for sql 2008):
--trace flags need to be set for the session (not sure if they would still be needed in later versions of sql
DBCC TRACEON (1165, 3605);
GO
EXEC sp_cycle_errorlog;
GO
USE [master];
GO
IF DATABASEPROPERTYEX (N'Company', N'Version') > 0
BEGIN
ALTER DATABASE [Company] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [Company];
END
GO
CREATE DATABASE [Company] ON PRIMARY (
NAME = N'Company_data',
FILENAME = N'D:\SQLskills\Company_data.mdf',
SIZE = 5MB,
FILEGROWTH = 1MB)
LOG ON (
NAME = N'Company_log',
FILENAME = N'D:\SQLskills\Company_log.ldf'
);
EXEC xp_readerrorlog;
GO
Now I’ll add a much larger file:
ALTER DATABASE [Company] ADD FILE (
NAME = N'ThirdFile',
FILENAME = N'D:\SQLskills\ThirdFile.ndf',
SIZE = 250MB,
FILEGROWTH = 1MB);
GO
EXEC xp_readerrorlog;
GO
Now I’ll add a much larger file:
ALTER DATABASE [Company] ADD FILE (
NAME = N'ThirdFile',
FILENAME = N'D:\SQLskills\ThirdFile.ndf',
SIZE = 250MB,
FILEGROWTH = 1MB);
GO
EXEC xp_readerrorlog;
GO
Now I’ll create a table that can have only one row per page, and force more than 8192 extent allocations to take place (by inserting more than 8192 x 8 rows, forcing that many pages to be allocated). This will also mean the files will have autogrown and will have roughly equal numbers of free extents.
USE [Company];
GO
CREATE TABLE [BigRows] (
[c1] INT IDENTITY,
[c2] CHAR (8000) DEFAULT 'a');
GO
SET NOCOUNT ON;
GO
INSERT INTO [BigRows] DEFAULT VALUES;
GO 70000
EXEC xp_readerrorlog;
GO
this below is not my idea or reseach - it is copied from the webpage - link above - thanks to Paul Randal and sql skills
Performance Implications
So when do you need to care about proportional fill?
One example is when trying to alleviate tempdb allocation bitmap contention. If you have a single tempdb data file, and huge PAGELATCH_UP contention on the first PFS page in that file (from a workload with many concurrent connections creating and dropping small temp tables), you might decide to add just one more data file to tempdb (which is not the correct solution). If that existing file is very full, and the new file isn’t, the skip target for the old file will be large and the skip target for the new file will be 1. This means that subsequent allocations in tempdb will be from the new file, moving all the PFS contention to the new file and not providing any contention relief at all! I discuss this case in my post on Correctly adding data file to tempdb.
The more common example is where a filegroup is full and someone adds another file to create space. In a similar way to the example above, subsequent allocations will come from the new file, meaning that when it’s time for a checkpoint operation, all the write activity will be on the new file (and it’s location on the I/O subsystem) rather than spread over multiple files (and multiple locations in the I/O subsystem). Depending on the characteristics of the I/O subsystem, this may or may not cause a degradation in performance.
Summary
Proportional fill is an algorithm that it’s worth knowing about, so you don’t inadvertently cause a performance issue, and so that you can recognize a performance issue caused by a misconfiguration of file sizes in a filegroup. I don’t expect you to be using trace flag 1165, but if you’re interested, it’s a way to dig into the internals of the allocation system.
