<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by bitbucket
»
Page split - 2
23 posts, Page 1 of 3
1
2
3
»
»»
Page split - 2
Rate Topic
Display Mode
Topic Options
Author
Message
bitbucket-25253
bitbucket-25253
Posted Saturday, August 06, 2011 2:39 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 5,123,
Visits: 20,369
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
mohammed moinudheen
mohammed moinudheen
Posted Sunday, August 07, 2011 10:59 PM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 10:29 PM
Points: 2,178,
Visits: 3,599
Great question . thanks :)
Mohammed Moinudheen
Post #1155817
hakan.winther
hakan.winther
Posted Monday, August 08, 2011 12:04 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226,
Visits: 438
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
Post #1155829
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Monday, August 08, 2011 1:59 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 3,164,
Visits: 4,344
Interesting question, thanks.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1155861
L' Eomot Inversé
L' Eomot Inversé
Posted Monday, August 08, 2011 5:30 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 7,185,
Visits: 7,285
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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
http://es.linkedin.com/in/tomthomsonsoftware
Post #1155927
Britt Cluff
Britt Cluff
Posted Monday, August 08, 2011 5:37 AM
SSCommitted
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
rfr.ferrari
rfr.ferrari
Posted Monday, August 08, 2011 5:59 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, June 06, 2013 4:06 PM
Points: 1,219,
Visits: 13,509
nice question!!!
rfr.ferrari
DBA
- SQL Server
2008
MCITP | MCTS
remember is live or suffer twice!
Post #1155948
calvo
calvo
Posted Monday, August 08, 2011 6:17 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,179,
Visits: 3,185
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
Thomas Abraham
Thomas Abraham
Posted Monday, August 08, 2011 6:27 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:11 AM
Points: 1,168,
Visits: 1,470
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.
Post #1155983
calvo
calvo
Posted Monday, August 08, 2011 6:47 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 1,179,
Visits: 3,185
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 »
23 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
