dbcc showcontig

• yvr4

  Default port

  Points: 1400

  More actions

  July 8, 2006 at 6:14 pm

  #62427

  I am running Dbcc showcontig  against one table  and i got following result.

  DBCC SHOWCONTIG scanning 'collector_status' table...

  Table: 'collector_status' (1125579048); index ID: 1, database ID: 18

  TABLE level scan performed.

  - Pages Scanned................................: 54495

  - Extents Scanned..............................: 6890

  - Extent Switches..............................: 13797

  - Avg. Pages per Extent........................: 7.9

  - Scan Density [Best Count:Actual Count].......: 49.37% [6812:13798]

  - Logical Scan Fragmentation ..................: 7.70%

  - Extent Scan Fragmentation ...................: 46.76%

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

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

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

  After running the following command

  DBCC DBREINDEX('dbo.collector_status', '', 90)

  GO

  I got following result

  DBCC SHOWCONTIG scanning 'collector_status' table...

  Table: 'collector_status' (1125579048); index ID: 1, database ID: 18

  TABLE level scan performed.

  - Pages Scanned................................: 47460

  - Extents Scanned..............................: 5962

  - Extent Switches..............................: 5961

  - Avg. Pages per Extent........................: 8.0

  - Scan Density [Best Count:Actual Count].......: 99.51% [5933:5962]

  - Logical Scan Fragmentation ..................: 0.03%

  - Extent Scan Fragmentation ...................: 52.65%

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

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

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

  Extent fragmentation is increased, i don't understand. how  can i reduce extent fragmentation?

   

   

   

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  July 10, 2006 at 12:46 am

  #648161

  from BOL :

• Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.

  Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.

  Is this the case ?

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