<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
»
SQL Server 2008
»
SQL Server 2008 Administration
»
rebuild on clustered index
27 posts, Page 1 of 3
1
2
3
»
»»
rebuild on clustered index
Rate Topic
Display Mode
Topic Options
Author
Message
muthyala_51
muthyala_51
Posted Thursday, November 08, 2012 10:00 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 6:11 PM
Points: 231,
Visits: 1,073
We have a heavy used table. We rebuild the indexex every night on this table. When the clustered index fragmentation is greater than 4, we rebuild the pk which releases almost 1/5 of free space to the database. Is this advisable to do it ? But if we don't the database will grow out of proportion. Any suggestions?
Post #1382601
MyDoggieJessie
MyDoggieJessie
Posted Thursday, November 08, 2012 10:22 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037,
Visits: 3,761
How many rows are in this heavily used table? 4% fragmentation really shouldn't be a big deal unless you've got millions of rows in the table and are adding/removing a similar amount of rows daily.
I am rather confused to your comment "we rebuild the pk which releases almost 1/5 of free space to the database". Your clustered index "is" the data and how it's stored in your data file. If you have 1,000,000 rows in it and there is 4% fragmentation, then rebuild it, you are still going to have 1,000,000 rows in it, except you should have 0% fragmentation. You're not going to see a space decrease/increase based upon rebuilding your clustered index.
How many non-clustered indexes do you have on this table? If you are rebuilding the clustered index nightly and aren't manually rebuilding your NCI's, SQL will automatically drop and recreate your NCI's after the clustered index has completed - this will take time and this could be where you see a space gain from.
Why do you need to rebuild at 4% fragmentation?
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1382617
muthyala_51
muthyala_51
Posted Thursday, November 08, 2012 11:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 6:11 PM
Points: 231,
Visits: 1,073
So you mean to say if we rebuild clustered index it rebuilds non clustered index. I don't think it does.
As said by you- this is what happens in our environment we have got millions of rows adding/removing a similar amount of rows daily. Can you explain in detail on how this affects?
We rebuilt all the indexes on this table yesterday, the space got reduced from 293 GB to 219 GB.
Post #1382662
MyDoggieJessie
MyDoggieJessie
Posted Thursday, November 08, 2012 1:45 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037,
Visits: 3,761
OH GOD I am sorry for that misleading statement (clearly not enough coffee this morning when I wrote that). No, NCI's
are not
affected by the rebuild of a clustered index. This occurs only if you DROP/recreate it
I am so sorry for that misleading statement!
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1382699
Evil Kraig F
Evil Kraig F
Posted Thursday, November 08, 2012 2:47 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 5,677,
Visits: 6,124
Rebuilding a clustered index is basically a drop/create and resets the internal additional RIDs for the index rows on the clustered, forcing all non-clustered indexes to be updated so they can keylookup again.
See this article from BOL:
http://technet.microsoft.com/en-us/library/ms189858(v=sql.100).aspx
The part you're interested in is under disabling nonclustered indexes:
When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata. The index is enabled when it is rebuilt. When the nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation.
Short form: REBUILD a clustered index, muck with everything. REORGANIZE a clustered index, and it's much more lightweight but may not get everything done.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions
|
Forum Netiquette
For index/tuning help, follow these directions.
|
Tally Tables
Twitter: @AnyWayDBA
Post #1382725
GilaMonster
GilaMonster
Posted Thursday, November 08, 2012 2:56 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
Evil Kraig F (11/8/2012)
Rebuilding a clustered index is basically a drop/create and resets the internal additional RIDs for the index rows on the clustered, forcing all non-clustered indexes to be updated so they can keylookup again.
No, it does not.
All the nonclustered indexes have in them is the clustering key, not the physical rids. Rebuild does not change the uniqueifier for non-unique clustered indexes. Rebuilding a clustered index does not rebuild nonclustered indexes (though there were bugs around this back in SQL 2000)
The piece quoted has to do with disabling and re-enabling an index, not just rebuilding it.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1382730
Evil Kraig F
Evil Kraig F
Posted Thursday, November 08, 2012 3:12 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 5,677,
Visits: 6,124
From the same MSDN article:
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 ...
If the Clustered Index is non-unique, it needs the Cluster + RID in the NC index, which would thus require an update of the information. What am I not understanding here?
I admit I mis-used the the quote above here in the page, and my apologies above for that, but if you rebuild the Clustered how is it not forced to re-key the non-clustered?
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions
|
Forum Netiquette
For index/tuning help, follow these directions.
|
Tally Tables
Twitter: @AnyWayDBA
Post #1382740
GilaMonster
GilaMonster
Posted Thursday, November 08, 2012 3:26 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
Evil Kraig F (11/8/2012)
From the same MSDN article:
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 ...
If the Clustered Index is non-unique, it needs the Cluster + RID in the NC index, which would thus require an update of the information. What am I not understanding here?
Not RID. uniqeifier.
Yes, the cluster plus uniquifier is in the NC index. The uniquifier does not change upon rebuild, hence the NC indexes don't need updating. It did and they did in SQL 2000, not since 2005 though.
That MSDN article is also incorrect in the "Rebuilding an index drops the index and creates a new one.". It's closer to create a new one then drop the old one (and I'm talking about offline rebuilds).
I don't have time right now to write up a proof, but it's not hard.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1382745
Evil Kraig F
Evil Kraig F
Posted Thursday, November 08, 2012 3:37 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 5,677,
Visits: 6,124
GilaMonster (11/8/2012)
Not RID. uniqeifier.
Yes, the cluster plus uniquifier is in the NC index. The uniquifier does not change upon rebuild, hence the NC indexes don't need updating. It did and they did in SQL 2000, not since 2005 though.
That MSDN article is also incorrect in the "Rebuilding an index drops the index and creates a new one.". It's closer to create a new one then drop the old one (and I'm talking about offline rebuilds).
I don't have time right now to write up a proof, but it's not hard.
Heh, it's not like I don't believe ya, but MSDN being a little off and your redescription makes a lot more sense. Apologies to all for my confusion.
Errr... Whoops?
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions
|
Forum Netiquette
For index/tuning help, follow these directions.
|
Tally Tables
Twitter: @AnyWayDBA
Post #1382753
MyDoggieJessie
MyDoggieJessie
Posted Thursday, November 08, 2012 5:58 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037,
Visits: 3,761
So now that we're all on board again :) The OP is asking "why" they are seeing this growth and reclaiming of space...?
"We rebuilt all the indexes on this table yesterday, the space got reduced from 293 GB to 219 GB."
And "But if we don't the database will grow out of proportion"
I don't see how rebuilding the clustered index would affect this growth unless they are referring to increase/decrease in space within the LDF file(s) - how would rebuilding the PK affect the size of a data file? Isn't growth within a the clustered index directly proportional to the amount of data being written into the table?
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1382785
« Prev Topic
|
Next Topic »
27 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.
