yourfirstserver.com

SQL Server Storage units to Manage SQL Database

Tweet

A SQL Server database makes use of two types of files. Data is first stored in a transaction log, which is one type of file, and then subsequently written to the second type, the database file. When a user enters a transaction, the data she inserts, updates, or deletes is sent along to the RDBMS engine, which records that line of data in the transaction log.

Primary Data Files

The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf. There can only be one primary data file.

Secondary Data Files

Secondary data files are optional, user-defined, data files. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended extension for secondary data files is .ndf.

Transaction Logs

Log files hold all the log information that can be used to recover the database. There must be at least one log file for each database. The recommended extension for log files is .ldf.

Let’s understand, How is the Data Stored inside a SQL Database?

Primary data files:

The primary data file is the starting point of the database and points to the other files in the database.

Secondary data files:

Secondary data files make up all the data files, other than the primary data file.

Log files:

Log files hold all the log information that is used to recover the database.

Data File Pages

Pages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Each file in a database has a unique file ID number. To uniquely identify a page in a database, both the file ID and the page number are required.

Pages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Each file in a database has a unique file ID number. To uniquely identify a page in a database, both the file ID and the page number are required.

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Now Lets’ discuss about page types used in the data files of a SQL Server database.

Page type ============= Data

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON. Index

Index entries.

Text/Image

Large object data types: text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, Variable length columns when the data row exceeds 8 KB: like varchar, nvarchar, varbinary, and sql_variant

Global Allocation, Shared Global Allocation Map

Information about whether extents are allocated.

Page Free Space

Information about page allocation and free space available on pages.

Index Allocation Map

Information about extents used by a table or index per allocation unit.

Bulk Changed Map

Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

Differential Changed Map

Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

Extents

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data.

SQL Server has two types of extents:

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

This is how SQL Server manages the data inside a database files.