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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19705 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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7459 Visits: 3913
Great question . thanks Smile

M&M
hakan.winther
hakan.winther
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: 3465 Visits: 612
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-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20599 Visits: 7449
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-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30261 Visits: 12598
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

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

http://brittcluff.blogspot.com/
rfr.ferrari
rfr.ferrari
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 13642
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4178 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4178 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