What is the difference/relationship between extent and allocation unit?

Can you explain the difference -or relationship- between ‘Extent’ and ‘Allocation Unit’ in SQL?

Answer

The allocation unit is basically just a set of pages. It can be small (one page) or large (many many pages). It has a metadata entry in sys.allocation_units. It is tracked by a IAM chain. The most common use of allocation units is the 3 well known AUs of a rowset: IN_ROW_DATA, ROW_OVERFLOW and LOB_DATA.

An extent is any 8 consecutive pages that start from a page ID that is divisible by 8. SQL Server IO is performed in an extent aware fashion: ideally an entire extent is read in at once, an entire extent is write out at once. This is subject to current state of the buffer pool, for details see How It Works: Bob Dorr’s SQL Server I/O Presentation. Extents are usually allocated together, so all pages of an extent belong to the same allocation unit. But since this would lead to overallocation for small tables a special type of extent is a so called ‘mixed’ extent, in which each page can belong to a separate allocation unit. For details see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps.

So as you see the concepts are related, but very different. Perhaps you should explain a bit what is the problem you’re trying to solve or why are you interested in these concepts, perhaps we can then elaborate.