Brad McGehee

index internals

Good morning Folks, I have gone through an article which is from Brad’s library.

We should appreciate him for giving us the best article on Indexes.

By understanding this Article we can get some extra knowledge on below

1. What is a Table?
2. What is an Index?
3. Types of data pages
4. What is a Heap?
5. Types of Indexes

We might have gone through various blogs on above topics, but this article will explore at least few new things for sure.

Apart from Indexes we can have a look at below:

Heap and B-Tree structures contain one or more allocation units. This is just a fancy way of subdividing different data types into three categories, which are:

In_Row_Data: This is the most common type of allocation unit, and is used to store data and index pages, except for Large Object (LOB) data. In other words, most of your row data (and related indexes) are stored in this type of allocation unit.

Lob_Data: Used to store Large Object (LOB) data, such as text, ntext, xml, image, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined data types.

Row_Overflow_Data: Used to store data pages when the variable data types—varchar, nvarchar, varbinary, and sql_variant data columns—that exceeds the 8,060 bytes that can fit onto a single data page.

Why are extents used to group eight 8K pages?

Extents are used because it is more resource efficient for SQL Server to allocate eight, 8K pages in a single step than it is for SQL Server to create eight, 8K pages in eight separate steps. There are two types of extents: uniform and mixed extents. All eight pages of a uniform extent are all owned by the same object. In a mixed extent, each page of the extent can be owned by a different object.

Why are there two different kinds of extents?

This is because many objects in a database are less than 64K in size. If only uniform extends were allowed, then there would be a lot of wasted space on extents that are not fully used by an object. By allowing mixed extends, multiple, smaller objects can all fit onto a single extent, which makes more efficient use of available space. Whenever a new table or index is created, it generally is first created on a mixed extent.

What happens if an object on a mixed event grows to exceed 64K?

If that happens, all future page allocations are done using uniform extents.

Here is the Full article: