1

I was looking at the SQL Server 2017 Documentation for page and extent allocations.

What I got to know from online resources and sessions in SQL conferences: Starting with SQL Server 2016, Uniform Extent Allocation is the default behavior. However, what MS documentation describes is:

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

  • 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

So, my question is: In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?

Improve this question
0

2 Answers 2

Reset to default
8

In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?

If the database was created in SQL Server 2016+ then by default the uniform extent allocation is on by default and thus a uniform extent will be allocated even if just a single page is needed (extents are 8 contiguous pages starting from database page 0).

If, however, the database was created in an older version <= SQL Server 2014 then by default the uniform extent allocation database setting did not exist and thus a single page from a shared extent was used. In <= 2014 there was a trace flag that would override this TF1118 setting but it would be for the entire instance and not just a single database. SQL Server 2016 took away the trace flag (which is no a NOP) and made the setting per database with a default of ON.

Improve this answer
2

Also note that some type of allocations are still from mixed extents. For instance IAM pages. So don't be suprised if you still se some mixed extent even for databases that lived the whole life in SQL Server 2016+ (with default settings)-

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.