SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Page split - 2


Page split - 2

Author
Message
bitbucket-25253
bitbucket-25253
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32061 Visits: 25280
Comments posted to this topic are about the item Page split - 2

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
M&M
M&M
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11484 Visits: 3919
Great question . thanks Smile

M&M
hakan.winther
hakan.winther
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3976 Visits: 613
Easy question, make this a multiple choice oR add a third option for "both" to make it slightly harder.

Thanks anyway.

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32862 Visits: 7660
Interesting question, thanks.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Tom Thomson
Tom Thomson
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47105 Visits: 13085
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

Britt Cluff
Britt Cluff
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3159 Visits: 253
Good straightforward question. Thanks.

http://brittcluff.blogspot.com/
rfr.ferrari
rfr.ferrari
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3489 Visits: 13648
nice question!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

calvo
calvo
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6698 Visits: 4018
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?

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Thomas Abraham
Thomas Abraham
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5739 Visits: 2256
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
calvo
calvo
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6698 Visits: 4018
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.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search