The Extent (A Storage Wednesday Post)

Introduction

A few weeks ago I said that SQL Server stores everything in pages, each 8192 bytes in size. Today I would like to revisit that statement and go into a little more detail of how SQL Server organizes all those pages.

Extents

A page is 8KB in size, which means 1MB of files space fits 128 pages. However, we do not really measure our file sizes in MB anymore. Database several hundred GB in size are not uncommon at all anymore. 100GB of file space contains over 13 million pages. To put that into perspective, one 8KB page contains just 65535 single bits.

The reason I am comparing counts of pages to counts of bits in a page is, that SQL Server needs to be able to quickly find an unused page when a table needs to grow. That means SQL Server needs a catalog somewhere indicating for each page if it is still available or not. To keep a catalog of free pages, you need at least a single bit per page. To make this a little more manageable, SQL Server groups pages into sections of eight. Each eight-page section is called an "Extent". An Extent is therefore 64KB in size and this is the smallest unit SQL Server will reserve when creating or growing a table.

Mixed Extents

One extent or 64KB is the smallest block that SQL Server will reserve in the file when growing a table. However, a table can still be smaller than 64KB. At the time this mechanism was introduced, 64KB was still a rather large chunk of disk space. To not waste space on disk when creating many small tables SQL Server introduced the concept of mixed extents (also called shared extents). A mixed extent contains pages that do not necessarily belong to the same table.

When a table is newly created and the first row is inserted, a single page is reserved for that table. That page is taken from any mixed extent that still has an unused page within. If there is no mixed extend with a free page available, a new extend is reserved on disk and marked as a mixed extent. Then one page from that new extend is allocated to the table. The next seven times the table has to grow, another page of any available mixed extent is allocated to the table.

Uniform Extents

Once the table grows beyond eight pages, every new allocation of space for that table will always be an entire extend. Extents that are allocated this way are called uniform extents.

Keep in mind however that an extend that was reserved as a mixed extent, stays a mixed extent event if it fills up with pages only from a single table.

Trace Flag 1118

There is some overhead involved with this flexibility and with today's storage prices you might want to consider disabling mixed extents completely in your databases. To achieve that you can enable trace flag 1118 by adding the -T1118 startup parameter to the SQL Server instance. There are several sources on the web that recommend to always turn TF 1118 on, e.g. Paul Randal. One of the main reasons is that it can help alleviate tempdb contention. That is also the reason why most posts about TF 1118 do not mention if this trace flag has any effect on other databases. However, you can be assured that it affects any new table, no matter, which database it is created in.

Summary

SQL Server stores all information split into 8KB blocks called pages. However, the smallest unit that SQL Server will reserve on disk when creating or growing a table is called an extent, which always consists of eight 8KB pages. SQL Server provides a mechanism to allow tables smaller the 64KB. This utilizes so-called mixed extents that can hold pages belonging to separate tables.