April 10, 2014 at 8:27 am
Hi
I'm looking through the output of DBCC IND. The second column is PagePID, the page number in the file. As I look through the results of this undocumented command on a table with only a clustered index, I see results like this:
PagePID
_______
1861
1870
2105
...
It was my belief that SQL allocated data pages to an index an extent at a time. If it did not do this, every extent would be mixed because the pages in it would be claimed by different tables as they grow. So I would expect the results to show:
PagePID
________
1861
1862
1863
...
This table I looked at has a primary key on the identity field, and the data is never updated or deleted, just inserted.
Can anyone provide an explanation of this?
Be still, and know that I am God - Psalm 46:10
April 10, 2014 at 8:34 am
When you create the object it will first get it's pages from a mixed extent after that you should be the PagePID return sequential order (for 8 pages) and then it make skip and then another contiguous 8 pages. Also without seeing the complete result I assume that you are looking at PageType = 1?
April 10, 2014 at 8:52 am
Yes, PageType = 1.
So if I'm understanding you correctly, when a table is first created the data is put into mixed extents. Once the data is big enough to fill a full extent, it will begin grabbing full extents as it grows.
So once the data is big enough to take a full extent, a rebuild on the index will get the data off the mixed extents and onto an extent dedicated to the table. Correct? That would be a reason defragging small indexes is pointless.
Be still, and know that I am God - Psalm 46:10
April 10, 2014 at 9:02 am
david.gugg (4/10/2014)
Yes, PageType = 1.So if I'm understanding you correctly, when a table is first created the data is put into mixed extents. Once the data is big enough to fill a full extent, it will begin grabbing full extents as it grows.
So once the data is big enough to take a full extent, a rebuild on the index will get the data off the mixed extents and onto an extent dedicated to the table. Correct? That would be a reason defragging small indexes is pointless.
Sounds like you got it. Here is an article about extents and pages. From the article:
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. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.
I would also recommend turning on Trace Flag 1118 which allocates full extents to Tempdb objects. http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
April 10, 2014 at 9:10 am
Great, thank you for the explanation.
Be still, and know that I am God - Psalm 46:10
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply