data page fragmentation

Question

Post reply

data page fragmentation

daniel.manke

SSC Veteran

Points: 242
More actions
June 10, 2008 at 11:56 am

#71647

I have some very heavily fragmented tables. Below are the results of DBCC SHOWCONTIG for 1 of those tables:
DBCC SHOWCONTIG scanning 'ELFTRANS_STATUSES' table...
Table: 'ELFTRANS_STATUSES' (1696165238); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 129965
- Extents Scanned..............................: 127196
- Extent Switches..............................: 127195
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 12.77% [16246:127196]
- Extent Scan Fragmentation ...................: 77.05%
- Avg. Bytes Free per Page.....................: 7824.4
- Avg. Page Density (full).....................: 3.33%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This Table has only 1 non clustered index that is stored in an index filegroup. My problem is that my primary filegroup is growing very large, and I believe the table data page fragmentation is part of my problem. How can I minimize this fragmentation? Can I rewrite the data pages so the data pages are more fully utilized?

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I would like to recommend

1, Check fillfactor, and reset it if necessary;

2. Reindex your table;

3. Run DBCC SHINKDB.

daniel.manke SSC Veteran Points: 242 More actions · Answer 2

The fill factor on the non clustered index on this table is 90. But my issue is not with the index... The index is stored in its own filegroup, and that filegroup is not very large. The issue is in the 3% usage of the data pages. Because of inserts and deletions on this table, it appears that the data pages are almost empty. So this table alone takes up 130,000 pages * 8KB/page = ~1GB

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Without a clustered index - you can't directly defragment that table (which by not having a clustered index is called a heap). Your only option would be to do a SELECT...INTO a new table, drop the old table and rename the new table to the old name.

Of course - that just resets the flag, and the fragmentation will start happening all over again.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Dan,

Would be better if you create a clustered index on this table(either on the same column on which non-clus index is thr or any other relevant column). This way you can control fragmentation.

If you don't have any suitable key for clustered index then better convert the existing non-clus index to clustered index.

Manu

daniel.manke SSC Veteran Points: 242 More actions · Answer 5

Thank you both for the responses. I have created a clustered index (similar to the current index, except not nique) on this table, and the size came way down, then I deleted the new clustered index. So that worked. The free space in the database file increased by about 5GB. Of course fragmentation may still occur moving forward. This table is part of a vendor software package. Not sure what effect changing indexes would have. I'll review with vendor...

Thanks.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

Dan,

Would be good if you go thru the following link before indulging in any argument with vendor regarding clustered vs. non-clus index.

http://www.sqlservercentral.com/articles/Indexing/clusterthatindexparttwo/1094/

Manu