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 12»»

Issue in reducing index fragmentation Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 4:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 13, 2014 8:13 AM
Points: 1,588, Visits: 1,556
Hi,
I have created a non-unique, non cluster index in one of the table. But when I run the query in my database
select * from sys.dm_db_index_physical_stats(db_id('Inventory'),null,null,null,null)

I can see that there is avg_fragmentation_in_percent of 75 for the newly created index. Ideally it should not have any fragmentation. Even after rebuilding and reorganizing the index also, the fragmentation is not getting reduced. It is showing the same.

Can anyone please explain me the reason?


Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #865963
Posted Tuesday, February 16, 2010 5:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
how many rows are in the table?
Post #866016
Posted Tuesday, February 16, 2010 5:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
How big's the table? How many pages?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #866021
Posted Tuesday, February 16, 2010 6:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
Also,
Object have cluster index ?


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #866043
Posted Tuesday, February 16, 2010 6:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
muthukkumaran (2/16/2010)
Object have cluster index ?


It's not required that a table have a clustered index in order to defragment a nonclustered index.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #866050
Posted Tuesday, February 16, 2010 6:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
GilaMonster (2/16/2010)
muthukkumaran (2/16/2010)
Object have cluster index ?


It's not required that a table have a clustered index in order to defragment a nonclustered index.


Yes I agreed Gail.

The OP create a Non clustered index,so i guess the table was Heap.



Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #866072
Posted Tuesday, February 16, 2010 7:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
muthukkumaran (2/16/2010)
The OP create a Non clustered index,so i guess the table was Heap.


How do you conclude that?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #866076
Posted Tuesday, February 16, 2010 8:43 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
Just guess....

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #866697
Posted Wednesday, February 17, 2010 6:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 13, 2014 8:13 AM
Points: 1,588, Visits: 1,556
Hi All ,
sorry for the late reply. Actually the table is very small it has only 399 rows and size is 234MB. Previously it was a heap table. Just for testing purpose I created a non-unique, non-clustered index on that table.


Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #866951
Posted Wednesday, February 17, 2010 6:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
You shouldn't worry about fragmentation on a table with that few rows.

MS recomends that you should not be concerned with fragementation of indexes with less than 1000 pages
Post #866953
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse