how do I take out the Fragmentation

Question

Post reply

how do I take out the Fragmentation

sqldba108

Newbie

Points: 9
More actions
April 8, 2012 at 9:53 am

#254461

Guy,
I have one database table and there is more then 60% fragmentation in that table. I try Reorganize Index and Rebuild Index but still there is same fragmentation on that table. I try drop Index but I am not able to drop that index because there is Primary key and Foreign key relationship. So can you please tell me how do I take out that fragmentation from database table.
Thanks,
Pinakin Patel

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Does your table contain a clustered index ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sqldba108 Newbie Points: 9 More actions · Answer 2

Ron,

Yes, I have clustered index on that table.

Thanks,

Pinakin

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

How big's the table? How many pages?

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

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

sqldba108 Newbie Points: 9 More actions · Answer 4

Gail,

Here is the all details;

DBCC SHOWCONTIG scanning 'Employee' table...

Table: 'Employee' (901578250); index ID: 1, database ID: 14

TABLE level scan performed.

- Pages Scanned................................: 8

- Extents Scanned..............................: 4

- Extent Switches..............................: 3

- Avg. Pages per Extent........................: 2.0

- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]

- Logical Scan Fragmentation ..................: 50.00%

- Extent Scan Fragmentation ...................: 75.00%

- Avg. Bytes Free per Page.....................: 1163.0

- Avg. Page Density (full).....................: 85.63%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Object_ID, Index_Id, ave_Fragmentation, Page_Count

9015782501508

901578250266.66666666666673

9015782503502

901578250401

901578250501

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

sqldba108 (4/8/2012)
- Pages Scanned................................: 8

And that is why rebuilding has no effect. Rebuilding has just about no effect on indexes of a couple of extents or smaller, and the rough guide to where you worry about fragmentation is 1000 pages. That index has 8.

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

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