Contents
How are extents allocated in SQL Server 2016?
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).
How big is the shared global allocation map in SQL?
One page is for the Index Allocation Map (IAM), and the second is for the first page for the object. SQL Server tracks mixed extents by using the Shared Global Allocation Map (SGAM) page. Each SGAM page tracks about 4 gigabytes of data.
How are pages allocated in a mixed extent?
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
How to determine file allocation unit size in SQL Server?
There are really good whitepapers with a recommendation to keep allocation unit size for SQL Server data and log drives at 64KB. 64KB presents one full extent or 8 * 8KB pages in SQL Server. To determine file allocation unit size for your drive you can run following command and for all drives. fsutil fsinfo ntfsinfo c:
What are the different types of extents in SQL Server?
There are two types of extents: mixed extents and dedicated (or uniform) extents. The first 8 pages that are allocated to any IAM chain (either for an index in SQL Server 2000 or allocation unit in SQL Server 2005) are single-page allocations, which are called mixed pages.
How are uniform extents and mixed extents allocated?
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.
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.