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 Monday, October 16, 2006 9:35 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
Comments posted here are about the content posted at temp
Post #315657
Posted Monday, October 23, 2006 2:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 13, 2007 12:12 PM
Points: 11, Visits: 1
Joe,
Thank you for the excellant article.
When I wrote a year ago my article about fragmentation, this is exactly what I had in mind - but since I am not an SQL expert, I could not do it myself.
Please read my article at http://www.sqlservercentral.com/columnists/kbiller/performancemonitoringbyinternalfragmentationmeasur.asp
and look at www.disklace.com where I present the fragmentation measurement model. This model is working for more than 100,000 users, and proves to be accurate. If you want to join forces and give the audience of this forum a solution - I am ready.
Koby Biller
Koby@disklace.com
Post #317231
Posted Monday, October 23, 2006 3:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 25, 2009 11:45 AM
Points: 184, Visits: 22
Thanks for the article cant wait for the next instalment. One thing that has confused me over the years is how sql stores and organises text data. You do not include this type of data field in your table,is their a reason for this? if you can just explain about text data as i find you explinations so far very easy to understand.


Post #317239
Posted Monday, October 23, 2006 4:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 13, 2014 4:39 AM
Points: 125, Visits: 276

Hi Joe

I really enjoyed this article.  I am not a SQL expert in any way, and found the step by easy step approach excellent.  I really feel I have learned something and time spent here was very worthwhile.  I look forward to the next article - particularly with a view towards indexes.

Many thanks for the time you spent on this.

Paul

Post #317248
Posted Monday, October 23, 2006 6:45 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
Very nice - I have learned a lot, cannot wait till the next article.

-- Cory
Post #317275
Posted Monday, October 23, 2006 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, Visits: 1
Good article, To the point. Cleared the basics! Keep up the good work.
Post #317278
Posted Monday, October 23, 2006 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:15 AM
Points: 29, Visits: 5

How this relates to the real world is this...

1) No clustered Index = A Heap

2) Add some Non-clustered indexes to a table and you still have a heap

3) Add more data with the non-clustered indexes and your heaped table will GROW by leaps and bounds !!

4) You want to make the table smaller BUT  a heap cannot be defragged! (nothing works, DBCC indexdefrag etc..)

5) You MUST create a clustered index to force defragmentation

Moral - NEVER create non-clustered indexes without creating a clustered index first!

I have come across this knowledge through evaluation of a legacy database which is massive for apparently no reason.  I could not shrink its size no matter what I did.  Only after much reading did I come across the "Heap Gem" of knowledge and was able to cut the DB down to 40% of its original size by adding, yes adding, clustered indexes to the top 30 largest tables.  There were hundreds of tables, dunno what would have happened by adding a CI to all appropriate tables.

- B

Post #317290
Posted Monday, October 23, 2006 9:19 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 13, 2014 4:39 AM
Points: 125, Visits: 276

Hi Billy

Great additional help to Joe's article.

Thanks for this.

Paul

Post #317345
Posted Monday, October 23, 2006 10:00 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

Great article Joe.  Thanks a bunch.  Your take on extent switching was new to me.  I'm in hopes that the rest of your series will help clear up a mystery for us.

Billy, I agree with you.  This goes into our mystery.  One table with less than 100 thousand rows had sporadic inserts take MINUTES to complete.  The majority of inserts take fractions of seconds.  All DBCC indications were clean. Re-index had been done and still it happened. A specific insert would hang.  If you drop the row and re-run that specific insert would take long.

In frustration I advised the guy working the problem to drop all the indexes and recreate them.  Most are non-clustered and the clustered is PK on the identity column.  Dropping the clustered index took 20 minutes. Re-creating the indexes seems to have fixed the problem.

What the ...?

 



ATB

Charles Kincaid

Post #317359
Posted Monday, October 23, 2006 2:06 PM


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

Mike,

I'll make sure information on all data types is included within this series. Thanks for your input.

Thanks to everyone who has contributed and shown an interest.

Joe

Post #317417
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse