August 6, 2011 at 2:39 pm
Comments posted to this topic are about the item Page split - 2
August 7, 2011 at 10:59 pm
Great question . thanks 🙂
M&M
August 8, 2011 at 12:04 am
Easy question, make this a multiple choice oR add a third option for "both" to make it slightly harder.
Thanks anyway.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 8, 2011 at 1:59 am
This was removed by the editor as SPAM
August 8, 2011 at 5:30 am
Nice simple question. Thanks for a gentle start to the week.
Uses an "undocumented" (maybe Paul Randall's blog entries is generally documentation as reliable as BoL, so maybe dbcc ind is not quite undocumented) dbcc function, it probably should have had a pointer to something about that in the explanation.
Given that it was only possible to select 1 answer, and rebuilding an index certainly isn't going to build any page splits, it ought to be be very difficult to get it wrong. But 36% of responders so far have achieved that.
Until I noticed that 36% figure I though tat adding a "both" option would make it only marginally harder, since it would still be a select one only question and it makes no sense to select any two of the three possibilities (if both were selected, both the others would logically have to be; and if it wasn't, only one of the others could be) so the SQLServerCentral QoTD code would make it a single choice question, thus givibg the game away. Making it a multiple choice question could make it a bit harder if enough answers plausible at first sight could be dreamt up, but I thought that would be rather difficult to achieve. So perhaps it should have just been "will ALTER INDEX...REORGANISE fix it or not", to make it difficult enough to be worth a whole point. The 36% wrong answers figure suggests that my thoughts were not in line with reality!
Tom
August 8, 2011 at 5:37 am
Good straightforward question. Thanks.
http://brittcluff.blogspot.com/
August 8, 2011 at 5:59 am
nice question!!!
August 8, 2011 at 6:17 am
For the sake of clarification, your question is which operation removes page splits. Are you referring to page splits as the fragmentation (logically or physically ordered pages/extents?) that gets removed in your answer? If so, both rebuild and reorganize remove fragmentation. If you are referring to compaction (removal of page splits?), as referenced in your resource, both rebuild and reorganize compact the index pages to the specified fill factor.
From your reference:
Rebuilding Indexes
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.
Reorganizing Indexes
Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
I'm confused because running the script provided, the dbcc command shows the same number of pages after each operation (no page splits).
Would you mind clarifying how a page split (define?) is handled in each operation?
August 8, 2011 at 6:27 am
Thanks for the great question.
I thought that "Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting ..." seemed clear enough.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
August 8, 2011 at 6:47 am
Thomas Abraham (8/8/2011)
Thanks for the great question.I thought that "Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting ..." seemed clear enough.
The clarification is because the reference states that both operations remove fragmentation and reclaim old space by compacting index pages.
August 8, 2011 at 6:52 am
Nice, thanks.
August 8, 2011 at 7:11 am
Thinking this can't be THAT easy, given the trick questions of late. But it was that easy. Good Monday question.
August 8, 2011 at 8:34 am
calvo (8/8/2011)
For the sake of clarification, your question is which operation removes page splits. Are you referring to page splits as the fragmentation (logically or physically ordered pages/extents?) that gets removed in your answer? If so, both rebuild and reorganize remove fragmentation. If you are referring to compaction (removal of page splits?), as referenced in your resource, both rebuild and reorganize compact the index pages to the specified fill factor.From your reference:
Rebuilding Indexes
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.
Reorganizing Indexes
Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
I'm confused because running the script provided, the dbcc command shows the same number of pages after each operation (no page splits).
Would you mind clarifying how a page split (define?) is handled in each operation?
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.
Tom
August 8, 2011 at 8:35 am
calvo (8/8/2011)
Thomas Abraham (8/8/2011)
Thanks for the great question.I thought that "Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting ..." seemed clear enough.
The clarification is because the reference states that both operations remove fragmentation and reclaim old space by compacting index pages.
But one of them says it's only done at leaf level, and the other doesn't. That makes a difference.
Tom
August 8, 2011 at 8:44 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.
Thank you for that excellent explanation.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply