Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Page split - 2 Expand / Collapse
Author
Message
Posted Saturday, August 06, 2011 2:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 5,473, Visits: 23,560
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
Post #1155603
Posted Sunday, August 07, 2011 10:59 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
Great question . thanks :)

Mohammed Moinudheen
Post #1155817
Posted Monday, August 08, 2011 12:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:33 PM
Points: 2,526, Visits: 530
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
Post #1155829
Posted Monday, August 08, 2011 1:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 3,694, Visits: 4,828
Interesting question, thanks.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1155861
Posted Monday, August 08, 2011 5:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
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
Post #1155927
Posted Monday, August 08, 2011 5:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Good straightforward question. Thanks.

http://brittcluff.blogspot.com/
Post #1155931
Posted Monday, August 08, 2011 5:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:25 PM
Points: 1,253, Visits: 13,546
nice question!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1155948
Posted Monday, August 08, 2011 6:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:32 AM
Points: 1,256, Visits: 3,510
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.
Post #1155968
Posted Monday, August 08, 2011 6:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 21, 2014 8:44 AM
Points: 1,633, Visits: 2,038
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
Post #1155983
Posted Monday, August 08, 2011 6:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:32 AM
Points: 1,256, Visits: 3,510
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.
Post #1155998
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse