Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes and Fragmentation in SQL Server 2000 Part 1


Indexes and Fragmentation in SQL Server 2000 Part 1

Author
Message
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715

You're not totally correct concerning a HEAP, some rdbms allow the table and index structures to be defined ( not all B Tree's by default ) heaps do not always fragment. IF the table is not subject to updates which cause deferred updates and there are no deletes then your heap will not fragment, all new data arrives on the top of the heap - hence the term. Secondary indexes on a heap can be defragmented without issue. Technically if your heap is a fifo ( first in first out ) then even the deletes would not fragment the table structure.

I agree that it's best practice to have a clustered index, and in fact most tables I see are still heaps , but with a clustered index. Also agreed that the leaf or data fragmentation in a table cannot be removed without a clustered index, however not all heaps fragment.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Clive Strong
Clive Strong
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1182 Visits: 6588
Nice article. Been doing lots of digging around with data pages/indexes/heaps of late as it's a hot topic where I work. Looking forward to seeing the next article.
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383

In the days before good databases were avaialable for PC's I was having to retrive data from random files in an ordered manner. We used bianry files to store the data and then built an index for the ordering (MySQL anyone?). We used a tree. Not exactly a B-tree.

First, we did not give a rip about balancing. The ordering of blocks within the index file was of almost no concern. Even at 233 clock speeds we were able to fetch the first record in an order in way under a second.

Performance suffered when we had to add all of the overhead of making it multi-user capable. This was in the days when the only networking that we had was shared file access.



ATBCharles Kincaid
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3792 Visits: 901

Thanks for the article, very nice, and I look foward to the continuation on this subject.

ThomBeaux



Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
B Hilderman
B Hilderman
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 40
Looking forward to someone being able to explain why extent scan fragmentation shows in the high eighties or ninties AFTER turning OFF sql server services... No change whatsoever...
Steve Calverley
Steve Calverley
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Thank you *very much* for a really interesting and useful article and I'll be watching closely for more installments.
Reecha Mishra
Reecha Mishra
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 115

It was a great article!!! I am waiting for the next article as this gives ignition to all thoughts regarding Fragmentation.


dbaforever
dbaforever
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 539

Thanks for an enlightening article, Joe.

You state: "Running any form of query on a heap will force a full scan of the table and could take a considerable amount of time. And the results from the DBCC SHOWCONTIG command, which is a tool for measuring fragmentation, is also of little use on a heap. "

I want to make sure I understand correctly. If TableA has a non-clustered index on ColumnA, but no clustered indexes..... and I select * from TableA Where ColumnA = 'abc'......... a table scan will be performed? I didn't know that. I thought by having an index on the column I'm querying on, that was sufficient to prevent a table scan (unless the table only has a few rows of course). Please elaborate.

Thanks, John


Joe Doherty-260822
Joe Doherty-260822
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 180

John,

You are right to point that out. Perhaps I should have made it clear that we were dealing with data that had NO indexes applied (clustered and non-clustered).

With part 1 of the series I simply wanted to get people thinking in terms of a heap of data stored in data pages. And to also introduce a few basics we could build on.
As you will see in the following parts we will introduce clustered and non-clustered indexes.

Joe


Laxma Reddy P
Laxma Reddy P
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 131

Joe,

Thanks alot for nice article. Would like to go for next part of this with not much gap. Hope you will post it very soon. By the way, I have question here...how data stores if we define a clustured index on a column which contains duplicate data and how it results when we SELECT it..please clarify.

Onceagain thanks for spending time for this good work.

-LRP


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search