table fragmentation

Question

table fragmentation

ramani Koneripalli

SSC Journeyman

Points: 77
More actions
March 11, 2004 at 10:46 am

#65883

We have a production table that is fragmented. Here is the output of the dbcc showcontig
Table: 'Stores' (259532008); index ID: 0, database ID: 22
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1564.5
- Avg. Page Density (full).....................: 80.67%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The table has only 1 index which is a non-clustered primary key and here is a output of the dbcc showcontig on the index -
Table: 'Stores' (259532008); index ID: 2, database ID: 22
LEAF level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7586.0
- Avg. Page Density (full).....................: 6.28%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How should I defragment this table ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 1

Given that there are only 2 pages in the table and 1 page in the index, I use these numbers to determine fragmentation. You need more data to get an accurate picture of fragmentation.

As far as defragmenting, you have two basic options: DBCC REINDEX and DBCC INDEXDEFRAG.

DBCC REINDEX essentially drops and rebuilds the indexes. It can be processor intensive, and holds locks on the the data it's working with. It should be used in off-hours when users aren't connected.

DBCC INDEXDEFRAG is a far less evasive process. It doesn't hold locks on resources, so it can be run while the database is in use. One of the main drawbacks is that given the size of the tables, it can take extremely long to run.

Check BOL for the full syntax of these commands.

Hope that helps!

ramani Koneripalli SSC Journeyman Points: 77 More actions · Answer 2

Thanks James.

I don't see any fragmentation at the index level but I do at the table level. Show contig output says that I have 50% logical fragmentation at the table level but 0% at the index level.

DBCC SHOWCONTIG scanning 'Stores' table...

Table: 'Stores' (259532008); index ID: 0, database ID: 22

TABLE level scan performed.

- Pages Scanned................................: 2

- Extents Scanned..............................: 2

- Extent Switches..............................: 1

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]

- Extent Scan Fragmentation ...................: 50.00%

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

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

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

At the index level -

DBCC SHOWCONTIG scanning 'Stores' table...

Table: 'Stores' (259532008); index ID: 2, database ID: 22

LEAF level scan performed.

- Pages Scanned................................: 1

- Extents Scanned..............................: 1

- Extent Switches..............................: 0

- Avg. Pages per Extent........................: 1.0

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

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 0.00%

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

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

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

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 3

It's comparing pages -- in the table you have a 1:2 ratio, and the index is a 1:1. Don't worry about any of this data yet --there aren't enough data pages to have any fragmentation indications relavant.

Now when you get 10000 data pages and you're at 50%, you've got issues

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

keep in mind you are working with a heap table ! (indid =0 )

If you want to defrag a heap, you'll have to put a clustering index on it.

check BOL

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me