November 8, 2012 at 10:00 am
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?
November 8, 2012 at 10:22 am
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; Theyll drag you down to their level and beat you with experience
November 8, 2012 at 11:39 am
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.
November 8, 2012 at 1:45 pm
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; Theyll drag you down to their level and beat you with experience
November 8, 2012 at 2:48 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 2:56 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2012 at 3:12 pm
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 3:26 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2012 at 3:37 pm
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? :blush:
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 5:58 pm
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; Theyll drag you down to their level and beat you with experience
November 8, 2012 at 7:54 pm
MyDoggieJessie (11/8/2012)
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?
It wouldn't
Mostly, yes.
I have to suspect there's a shrink (auto or manual) involved somewhere in this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2012 at 8:01 am
Auto shrink on the database is set to false. I changed the threshold to rebuild index when greater than 10 , the database grew to 280 GB in no time. As I said this table has heavy insert and deletes(around 7M).
November 9, 2012 at 9:00 am
Rebuilds are not going to affect how a DB grows. There's something else going on (check for manual shrinks in with the index rebuild job)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2012 at 11:09 am
Don't you think as million of rows are getting inserted and deleted daily can cause high fragmentation which causes the database size to grow. When rebuilt the fragmented space is released. The query used for rebuild--
Alter Index index_name ON Table Rebuild With (Online = ON, Sort_in_TempDB = ON)
November 9, 2012 at 11:38 am
muthyala_51 (11/9/2012)
Don't you think as million of rows are getting inserted and deleted daily can cause high fragmentation which causes the database size to grow. When rebuilt the fragmented space is released. The query used for rebuild--Alter Index index_name ON Table Rebuild With (Online = ON, Sort_in_TempDB = ON)
Yes it will cause fragmentation and it will definitely cause your DB/data file to grow. If you delete roughly the same amount of records, this space will still be consumed by your DB/data file (as the file physically grew on disk, but much of it will be free within SQL). Rebuilding the index/PK should not cause anything to free up space...
Your DB needs to be sized appropriately to handle this daily growth. Without knowing your indexing strategy, I can only assume that somewhere within that process there's a SHRINKFILE/SHRINKDATABASE going on :crazy: that clears out your log file and then reduces the data file back to a set size before the growth (not an ideal strategy (fyi))
If this assumption is correct, I recommend you size your DB appropriately and keep it there. Your DB is only going to grow based upon the number of records you're adding to it...it's not going to keep growing, it's going to peak out at a certain size and stay there (or until more is added to it).
Note: IMHO - SORT_IN_TEMPDB prolly shouldn't be used for such a large index if we're talking millions of rows
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply