Have you ever wondered how SQL Server stores data in its data files? While data in tables is logically organized in rows and columns, it is physically stored in data pages within the data files of the database. In this article, we will explore how pages are allocated to data files and what happens when there are multiple data files for a SQL Server database.

Data Files and Filegroups

Every SQL Server database consists of at least two operating system files: a data file and a log file. The primary data file contains startup information for the database and points to other files in the database. User data and objects can be stored in this file, and every database has one primary data file. Secondary data files, on the other hand, are optional and can be used to spread data across multiple files or disks by placing each file on a different drive.

SQL Server databases can have multiple data and log files, but only one primary data file. These files are organized into filegroups, which serve as logical containers for the data files. A filegroup can have multiple data files, allowing for better management and distribution of data across different storage devices.

Data Pages and Proportional Fill

The disk space allocated to a data file is logically divided into pages, which are the fundamental unit of data storage in SQL Server. Each database page is an 8 KB chunk of data. When you insert data into a SQL Server database, it is saved to a series of 8 KB pages within the data file.

If multiple data files exist within a filegroup, SQL Server allocates pages to all data files based on a round-robin mechanism. This means that when you insert data into a table, SQL Server will allocate pages first to data file 1, then to data file 2, and so on, before going back to data file 1 again. This allocation is achieved through an algorithm known as Proportional Fill.

The proportional fill algorithm ensures that data is evenly distributed across all data files within a filegroup. It determines the amount of information that should be written to each data file based on the proportion of free space within each file. This allows the files to become full at approximately the same time, preventing any single file from becoming overloaded with data.

Analyzing Data Storage

To better understand how SQL Server stores data, let’s walk through a step-by-step analysis:

Step 1: Creating the Database and Data Files

We start by creating a database with multiple data files. In this example, we create a database named “Manvendra” with one primary data file and two secondary data files. Each file is allocated a specific size and has a defined file growth.

CREATE DATABASE [Manvendra]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Manvendra', FILENAME = N'C:\MSSQL\DATA\Manvendra.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_1', FILENAME = N'C:\MSSQL\DATA\Manvendra_1.ndf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_2', FILENAME = N'C:\MSSQL\DATA\Manvendra_2.ndf' ,SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
 LOG ON
( NAME = N'Manvendra_log', FILENAME = N'C:\MSSQL\DATA\Manvendra_log.ldf',SIZE = 10MB , MAXSIZE = 1GB , FILEGROWTH = 10%)

Step 2: Checking Free Space in Data Files

We can check the available free space in each data file using the sys.database_files system view. This allows us to track the sequence of page allocations to the data files.

USE Manvendra
GO
SELECT DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
FROM sys.database_files

Step 3: Creating a Table and Inserting Data

We create a table and insert data into it to observe how SQL Server allocates pages to the data files.

USE Manvendra;
GO
CREATE TABLE [Test_Data] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore',
    [Name] CHAR (25) DEFAULT 'Manvendra Deo Singh');

Step 4: Checking Allocated Pages and Free Space

We check the allocated pages and free space available in each data file after inserting data into the table.

USE Manvendra
GO
SELECT DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
FROM sys.database_files

Step 5: Inserting More Data and Checking Allocation

We insert additional data into the table and observe how SQL Server continues to allocate pages to the data files.

USE Manvendra
GO
INSERT INTO Test_DATA DEFAULT VALUES;
GO 10000

Step 6: Analyzing Allocated Pages and Free Space

We check the available free space and allocated pages in each data file after inserting more data.

USE Manvendra
GO
SELECT DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
FROM sys.database_files

Step 7: Verifying Data Storage

We can verify where the data is stored for the table by using the DBCC IND command.

DBCC IND ('Manvendra', 'Test_data', -1);

Step 8: Repeating the Exercise

We repeat the same exercise of inserting more data and checking the allocation to validate the proportional fill algorithm.

USE Manvendra
GO
INSERT INTO Test_DATA DEFAULT VALUES;
GO 10000
SELECT DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
FROM sys.database_files

By following these steps, we can observe how SQL Server allocates pages to data files and ensures proportional fill across multiple files within a filegroup. This understanding can help in optimizing data storage and performance in SQL Server databases.

Remember, the proportional fill algorithm is just one aspect of SQL Server’s data storage and allocation strategy. There are many other factors and considerations that come into play, such as index structures, partitioning, and filegroup placement. It is important to have a comprehensive understanding of these concepts to effectively manage and optimize your SQL Server databases.