Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Indexes and Fragmentation in SQL Server 2000 Part 1 Expand / Collapse
Author
Message
Posted Tuesday, October 24, 2006 3:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

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/
Post #317497
Posted Tuesday, October 24, 2006 3:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:37 AM
Points: 1,120, Visits: 6,474
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.
Post #317501
Posted Tuesday, October 24, 2006 8:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 808, Visits: 1,994

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.



ATB

Charles Kincaid

Post #317591
Posted Tuesday, October 24, 2006 9:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:15 PM
Points: 3,147, Visits: 794

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/
Post #317623
Posted Tuesday, October 24, 2006 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 5, 2008 9:56 AM
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...
Post #317722
Posted Tuesday, October 24, 2006 3:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 24, 2006 3:37 PM
Points: 1, Visits: 1
Thank you *very much* for a really interesting and useful article and I'll be watching closely for more installments. 
Post #317741
Posted Wednesday, October 25, 2006 12:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:16 AM
Points: 15, Visits: 109

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

Post #317811
Posted Wednesday, October 25, 2006 6:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499

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

 

 

Post #317884
Posted Wednesday, October 25, 2006 6:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 31, 2014 6:01 PM
Points: 64, Visits: 137

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

Post #317895
Posted Thursday, October 26, 2006 1:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 14, 2011 6:23 PM
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

Post #318207
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse