May 28, 2012 at 8:58 am
Hi,
I read in an article that clustered indexes do not necessarily guarantee the correct order of records especially large tables . This confuses me a bit . How will we be able to reindexing of these tables if there are no clustered indexes ? Any ideas ?
May 28, 2012 at 9:01 am
Correct. All an index (clustered or otherwise) guarantees it the logical order of the rows and pages.
If a clustered index did guarantee the physical order then there would never be any need to defragment a clustered index as fragmentation is defined as the logical and physical ordering. Defragmenting an index is the process of putting the index back into physical order (mostly)
p.s. You can only reindex an index. A table without a clustered index is a heap and that can't be reindexed
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
May 28, 2012 at 9:03 am
this would be a good place to start your investigation
May 28, 2012 at 1:40 pm
Giela Monster,
thank you for the feedback. I have another question though . How will one address a table with high volumes of inserts and deletes ? Surely the rebuilding on the indexes become useless due to the high volume of overhead . I also read one should not add a clustered index to such a table . Is it stil the case with 2208R2 ?
May 28, 2012 at 1:50 pm
All tables should have a clustered index unless you know better (and I don't mean having read something) Been that way since SQL 7.
For frequent inserts, make sure the clustered index is well-designed.
http://qa.sqlservercentral.com/articles/Indexing/68439/
http://qa.sqlservercentral.com/articles/Indexing/68563/
http://qa.sqlservercentral.com/articles/Indexing/68636/
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
May 28, 2012 at 5:18 pm
GilaMonster (5/28/2012)
Correct. All an index (clustered or otherwise) guarantees it the logical order of the rows and pages.If a clustered index did guarantee the physical order then there would never be any need to defragment a clustered index as fragmentation is defined as the logical and physical ordering. Defragmenting an index is the process of putting the index back into physical order (mostly)
p.s. You can only reindex an index. A table without a clustered index is a heap and that can't be reindexed
Do you know when ALTER TABLE ... REBUILD was introduced? I thought it was 2008 but may have been 2008 R2. I believe that can be used to rebuild a heap.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2012 at 5:25 pm
Jeffrey Williams 3188 (5/28/2012)
Do you know when ALTER TABLE ... REBUILD was introduced? I thought it was 2008 but may have been 2008 R2. I believe that can be used to rebuild a heap.
2008. It's there to compress (or decompress) a heap. It doesn't do what an index rebuild does because heaps have no logical order. It does (iirc) remove forwarding pointers and neaten up the pages in terms of free space.
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
May 28, 2012 at 6:41 pm
GilaMonster (5/28/2012)
Jeffrey Williams 3188 (5/28/2012)
Do you know when ALTER TABLE ... REBUILD was introduced? I thought it was 2008 but may have been 2008 R2. I believe that can be used to rebuild a heap.2008. It's there to compress (or decompress) a heap. It doesn't do what an index rebuild does because heaps have no logical order. It does (iirc) remove forwarding pointers and neaten up the pages in terms of free space.
Yes, it completely rebuilds the heap structure. Any non-clustered indexes will also be rebuilt because the RIDs will generally change.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2012 at 1:19 pm
Using ALTER TABLE ... REBUILD can actually make things worse in terms of fragmentation depending on the heap's DDL. If you're exploring the fragmentation of a heap as a means to improve performance then it's time to seriously consider adding a clustered index.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 29, 2012 at 3:46 pm
opc.three (5/29/2012)
Using ALTER TABLE ... REBUILD can actually make things worse in terms of fragmentation depending on the heap's DDL.
What do you mean?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2012 at 10:55 pm
SQL Kiwi (5/29/2012)
opc.three (5/29/2012)
Using ALTER TABLE ... REBUILD can actually make things worse in terms of fragmentation depending on the heap's DDL.What do you mean?
I mean that fragmentation reported in column sys.dm_db_index_physical_stats.avg_fragmentation_in_percent can increase after running ALTER TABLE ... REBUILD. The issue is far more pronounced when adding a clustered index and then immediately dropping it in an attempt to defragment a heap, however the same behavior can be seen when using ALTER TABLE ... REBUILD as well.
Maybe a year ago I looked around to see if anyone had produced guidelines for when to defragment heaps, similar to the made up guidelines for index fragmentation, but I came up empty. In trying to nail down a good starting point of my own I discovered the issue where fragmentation can actually increase after adding and dropping a clustered index. I'll hedge here a little because heaps do have a place in this world so it will depend, but I still think that if anyone has begun to explore defragmenting a heap as a means to improve performance then it's time they seriously consider adding a clustered index.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 30, 2012 at 12:18 am
opc.three (5/29/2012)
I mean that fragmentation reported in column sys.dm_db_index_physical_stats.avg_fragmentation_in_percent can increase after running ALTER TABLE ... REBUILD.
I suppose it might, it's not something I've ever noticed though. I just ran a quick test and REBUILD seemed to decrease extent fragmentation every time (as you would expect) at least so long as the operation was not performed ONLINE. I suppose one might care about extent fragmentation a little from a read-ahead point of view, but it really doesn't make much difference, and how often do you perform a full scan of a heap anyway? That's what the non-clustered indexes are for.
The issue is far more pronounced when adding a clustered index and then immediately dropping it in an attempt to defragment a heap, however the same behavior can be seen when using ALTER TABLE ... REBUILD as well.
Yes but this is a bit of an odd thing to do - it involves rebuilding the non-clustered indexes twice. REBUILD is much better. I still don't understand why extent fragmentation should keep one awake at night though.
Maybe a year ago I looked around to see if anyone had produced guidelines for when to defragment heaps, similar to the made up guidelines for index fragmentation, but I came up empty. In trying to nail down a good starting point of my own I discovered the issue where fragmentation can actually increase after adding and dropping a clustered index.
There's probably no guidelines because it's quite an edge case. Unlike logical fragmentation (which only occurs in indexes), extent fragmentation is a much smaller issue.
I'll hedge here a little because heaps do have a place in this world so it will depend, but I still think that if anyone has begun to explore defragmenting a heap as a means to improve performance then it's time they seriously consider adding a clustered index.
I tend to agree in general that most tables ought to have a clustered index, but there are definitely cases where heaps make good sense.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 7:37 am
SQL Kiwi (5/30/2012)
The issue is far more pronounced when adding a clustered index and then immediately dropping it in an attempt to defragment a heap, however the same behavior can be seen when using ALTER TABLE ... REBUILD as well.
Yes but this is a bit of an odd thing to do - it involves rebuilding the non-clustered indexes twice. REBUILD is much better. I still don't understand why extent fragmentation should keep one awake at night though.
True, but in 2005 add/drop clustered was our only option. In my testing on 2005 I would drop the non-clustered indexes, add a clustered, drop the clustered, then re-add the non-clustered indexes to avoid that double-non-clustered-index-rebuild. When considering automating the process I looked into disabling the non-clustered indexes so I would not have to figure out the DDL on the fly before dropping and re-adding but IIRC non-clustered indexes are rebuilt whenever a clustered index is added to a heap which defeated the attempt at simplifying the process. At any rate, at one point extent fragmentation had threatened to keep me awake at night but I learned about heaps and formed the opinion I shared earlier. It sounds fairly consistent with yours and given that you have a much deeper knowledge in this area I take comfort in that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 30, 2012 at 3:01 pm
Tables with fewer than eight pages can see increased fragmentation when rebuilt, because SQL may spread the table across more extents.
Personally I don't like SQL's mixed extents, but I can't really prevent it. The relevant trace flag helps some, but it's not 100%.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 30, 2012 at 4:14 pm
opc.three (5/30/2012)
... It sounds fairly consistent with yours and given that you have a much deeper knowledge in this area I take comfort in that.
Ha well I don't know about that - I only asked originally in case you were aware of something I should know 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply