This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Tell me something about the SQL Server Pages.
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.
What is a row offset of pages?
For each data row there is a 2-byte entry, saving the offset.
A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page.
What is the ROW_OVERFLOW_DATA allocation unit?
When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.
What is a IN_ROW_DATA allocation unit?
It is an allocation unit for data page and index page.
What is a LOB_DATA allocation unit?
It is an allocation unit for text, ntext, image, xml, varchar (max), nvarchar (max), varbinary (max) data types.
Why 24-byte pointer is require with the ROW_OVERFLOW_DATA allocation unit?
When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this 8KB limit, the Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page.
What is the size of the data page header?
The size of the data page header is 96-byte.
What are the different types of pages and what do they store?
- Page types include
- Data page
- Index page
- Text/Image page
- Page Free Space
- Global Allocation Map (GAM) – extent allocation information
- Shared Global Allocation Map (SGAM) – mixed extent with at least one unused page information
- Bulk Changed Map – extents that got changed by a bulk logged operation
- Differential Changed Map – extents that got changed since last BACKUP DATABASE statement
- Index allocation map
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.
What are the different 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.
What are the different types of database files in SQL Server?
- Primary data files: The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
- Secondary data files: Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.
- Log files: Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.
Explained something about SQL Server Access Methods.
- SQL doesn’t directly retrieve from disk, it makes requests to buffer manager which serves up the page in cache before rendering out.
- When scan starts, SQL Server can use look-ahead mechanisms to qualify rows and index entries on a page. The retrieving of rows that meet specified criteria is known as a qualified retrieval.
Tell us something about the SQL Server Database Transactions.
- Provides support for Atomicity, Consistency, Isolation and Durability.
- Log records are always written to disk before the data pages where changes were made are actually written.
- Writes to the transaction log are synchronous while writes to data pages can be asynchronous.
What is the sparse file?
A database snapshot that is created by a user stores its data in one or more sparse files. Sparse file technology is a feature of the NTFS file system.
What is the Database Filegroups?
Database objects and files can be grouped together in filegroups for allocation and administration purposes.
What are the different type of Filegroups?
- Primary: The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup.
- User-defined: User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
What are Page Splits?
When there is not enough room on a page for a new row, a server splits the page, allocates a new page, and moves some rows to the new page.
Page splits are considered as a very bad performance and there are number of techniques which is used to reduce the risk of page splits.
