August 8, 2011 at 9:02 am
Great explanation Tom, Thank you.
M&M
August 8, 2011 at 9:10 am
Tom.Thomson (8/8/2011)
The key to the difference in the section you quote is the phrase "leaf level" in the description for Reorganising Indexes. I'll try to explain what happens, why "leaf level" really does make a difference to the answer.Before the page split, the index contains just one page (which contains all the data in the table). The single index page is both the root of the index and the only leaf page of the index. (there is an information page too, of course, so there are two pages in all).
The page split adds two extra pages to the index: an extra level 0 (leaf) page, and a level 1 page to be the root of the index, which is needed because there are now two leaf pages. There are now three index pages and one information page.
REORGANISE deals only with leaf pages, so although it removes the extra leaf page it retains the now redundant level 1 page to point to the single level 0 page. There are now two index pages, one of which (the leaf) contains all the data in the table, and one information page. The index is still split into two pages, not reduced into one page, because reorganise does only compaction within the leaf level, not withinn higher index levels and not the inter-level compaction needed here.
REBUILD deals with pages at all levels of the index, not just leaf pages; so after rebuild there will be only a single level 0 index page, which is both the root and the sole leaf of the index. Now there is one index page and one information page, exactly as before the page split occurred; so REBUILD has eliminated the page split, while REORGANISE left the index split into two pages.
Excellent, thank you Tom.
I suspected there was more to it than just splits at the leaf level, I just wasn't sure how the operations handled the different levels of the index. So when the reference uses the term "compaction", that is the action that resolves the split issue? and the difference is that reorganize only compacts the leaf level whereas the rebuild is able to compact the intermediate levels of the index via creating a new index.
August 8, 2011 at 10:35 am
Great question. A good example to explain the differences with these two DBCC operations.
August 8, 2011 at 11:05 am
Nice question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 8, 2011 at 11:22 am
A very good, thoughtful question. Much thanks!
August 8, 2011 at 1:45 pm
thanks for the question and thanks for the additional explanations...
August 9, 2011 at 12:40 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 10, 2011 at 1:15 pm
Your question is broken.
The correct answer is the rebuild, but not for the reason you say. The table in question will not have more than 8 pages and so they will all be mixed-page allocations, not from a dedicated extent. Reorganize won't work in this case because it doesn't operation on mixed pages (I wrote it). Extent fragmentation is not relevant to the discussion of this example.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply