September 19, 2003 at 4:23 pm
I find these two conflicting statements in books online, while I am trying to interpret the results of DBCC Showcontig and the logical and extent fragmentation.
In the "Managing Space Used by Objects" section it states:
SQL Server allocates a new extent to an object only when it cannot quickly find a page in an existing extent with enough space to hold the row being inserted. SQL Server allocates extents from those available in the filegroup using a proportional allocation algorithm. If a filegroup has two files, one of which has twice the free space of the other, two pages will be allocated from the file with more empty space for every one page allocated from the other file.
Then in "Pages and Extents" section it states:
Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB.
How can it assign pages for an extent from different files and maintain they are contiguous?? What am I missing here? Are all pages in extent contiguous in the DB files or can they come from different files so not contiguous.
I see a table with large extent fragmentation, and I am trying to understand whether it has anything to do with the multiple files the object is located on.
Thanks.
September 19, 2003 at 7:25 pm
OK, let me try this one, this is an excellent question in my humble opinion.
You have two types of extents, Mixed and Uniform. In the first statement it indicates that a uniform extent is created if there is no space left in available extents. On the next line they talk about allocating to a mixed extent which can span files (and would not be contigous).
From Delaney's Inside SQL Server:
"The page manager allocates and deallocates all types of disk pages, organizing extents of eight pages each. An extent can be either a uniform extent, for which all eight pages are allocated to the same object (table or index), or a mixed extent, which can contain pages from multiple objects. If an object uses fewer than eight pages, the page manager allocates new pages for that object from mixed extents. When the size of the object exceeds eight pages, the page manager allocates new space for that object in units of entire uniform extents. This optimization prevents the overhead of allocation from being incurred every time a new page is required for a large table; this overhead is incurred only every eighth time. Perhaps most important, this optimization forces data of the same table to be contiguous, for the most part. At the same time, the ability to use mixed extents keeps SQL Server from wasting too much space if a database contains many small tables."
This is why when you run DBCC SHOWcontig you may see fragmentation at the extent level (caused by mixed extents) So, I think the general claim of all extents being contigous is inaccurate.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply