how to read dbcc showcontig result

Question

question

matta1985 asked Jul 30, '13

how to read dbcc showcontig result

When see the result of dbcc showcontig(tablename) am getting scandensity value 38% Then what should i do can i go for rebuild or reorganize. Pla help for me.

results

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-07-31T08:55:00Z

uricken answered Jul 31, '13

Hallo matta, simple answer: it depends :) The percentage value given by dbcc showcontig alone isn't a value for activities. How large is the relation? How many records do you have in your relation. Can you please show the results of sys.dm_db_index_physical_stats USE [YourDB]; GO SELECT index_type_desc, index_level, avg_fragmentation_in_percent, fragment_count, page_count, record_count FROM sys.dm_db_index_physical_stats ( db_id(), OBJECT_ID('YourTableName', 'U'), DEFAULT, DEFAULT, 'DETAILED' );

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

matta1985 commented · Jul 31, 2013 at 10:36 AM

Hi am sending the result of sys.dm_db_index_physical_stats please see and give the suggestion. index_type index_level fragmention_in_percent > CLUSTERED INDEX 0 0.512867200776888 3715 32952 41874 > CLUSTERED INDEX 1 1.23456790123457 162 162 32952 > CLUSTERED INDEX 2 0 1 1 162 > CLUSTERED INDEX 0 0 NULL 94696 108647

0 ·

matta1985 commented · Jul 31, 2013 at 10:41 AM

![alt text][1] [1]: /storage/temp/1025-untitled.png

0 ·

untitled.png (10.7 KiB)

uricken commented · Jul 31, 2013 at 10:56 AM

Hallo Matta, my recommendation is a REORG of the clustered index. The fragmentation is under 30% and the page count is >= 10.000 this is a "standard" recommendation http://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

0 ·

question