Index Storage Fundamentals
Pages are used by SQL Server to store everything in the database
When space is allocated to database data files, all of the space is divided into pages. During allocation, each page is created to use 8KB (8192 bytes) of space and they are numbered starting at 0 and incrementing 1 for every page allocated. When SQL Server interacts with the database files, the smallest unit in which an I/O operation can occur is at the page level.
There are three primary components to a page: the page header, records, and offset array, as shown in Figure 2-1.
All pages begin with the page header. The header is 96 bytes and contains meta-information about the page, such
as the page number, the owning object, and type of page. At the end of the page is the offset array. The offset array
is 36 bytes and provides pointers to the byte location of the start of rows on the page. Between these two areas are
8060 bytes where records are stored on the page.
Page Types
They above Category we can discuss only most important as see below
Data Page
Data pages are likely the most prevalent type of pages in any database. Data pages are used to store the data
from rows in the database’s tables. Except for a few data types, all data for a record is located on data pages. The
exception to this rule is columns that store data in LOB data types.
Index Page
Similar to data pages are index pages. These pages provide information on the structure of indexes and where
data pages are located. For clustered indexes, the index pages are used to build the hierarchy of pages that are
used to navigate the clustered index. With non-clustered indexes, index pages perform the same function but are
also used to store the key values that comprise the index.
SQL Server utilizes a number of different organizational structures for storing data in the database
• Heap
• B-Tree
• Columnar
Heap Structure
The default structure for organizing pages is called a heap.
a heap can be envisioned to be a pile of data pages in no particular order,
B-Tree Structure or Balanced-tree
The second available structure that can be used for indexing is the Balanced-tree, or B-tree, structure
organizing indexes in SQL Server and is used by both clustered and nonclustered
indexes.
In a B-tree, pages are organized in a hierarchical tree structure,
Column Store Structure
used by the new column store non-clustered index type
Pages are used by SQL Server to store everything in the database
When space is allocated to database data files, all of the space is divided into pages. During allocation, each page is created to use 8KB (8192 bytes) of space and they are numbered starting at 0 and incrementing 1 for every page allocated. When SQL Server interacts with the database files, the smallest unit in which an I/O operation can occur is at the page level.
There are three primary components to a page: the page header, records, and offset array, as shown in Figure 2-1.
All pages begin with the page header. The header is 96 bytes and contains meta-information about the page, such
as the page number, the owning object, and type of page. At the end of the page is the offset array. The offset array
is 36 bytes and provides pointers to the byte location of the start of rows on the page. Between these two areas are
8060 bytes where records are stored on the page.
Page Types
- File header page
- Boot page
- Page Free Space (PFS) page
- Global Allocation Map (GAM) page
- Shared Global Allocation
- Map (SGAM) page
- Differential Changed Map (DCM) page
- Bulk Changed Map (BCM) page
- Index Allocation Map (IAM) page
- Data page
- Index page
- Large object (Text and Image) page
They above Category we can discuss only most important as see below
Data Page
Data pages are likely the most prevalent type of pages in any database. Data pages are used to store the data
from rows in the database’s tables. Except for a few data types, all data for a record is located on data pages. The
exception to this rule is columns that store data in LOB data types.
Index Page
Similar to data pages are index pages. These pages provide information on the structure of indexes and where
data pages are located. For clustered indexes, the index pages are used to build the hierarchy of pages that are
used to navigate the clustered index. With non-clustered indexes, index pages perform the same function but are
also used to store the key values that comprise the index.
SQL Server utilizes a number of different organizational structures for storing data in the database
• Heap
• B-Tree
• Columnar
Heap Structure
The default structure for organizing pages is called a heap.
a heap can be envisioned to be a pile of data pages in no particular order,
B-Tree Structure or Balanced-tree
The second available structure that can be used for indexing is the Balanced-tree, or B-tree, structure
organizing indexes in SQL Server and is used by both clustered and nonclustered
indexes.
In a B-tree, pages are organized in a hierarchical tree structure,
Column Store Structure
used by the new column store non-clustered index type
No comments:
Post a Comment