index > SQL Server Database Engine > Defrag table/indexes

Defrag table/indexes

This is the output of my dbcc showcontig ('Tb_MyTable')

DBCC SHOWCONTIG scanning 'Tb_MyTable' table...
Table: 'Tb_MyTable' (1652200936); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 488
- Extents Scanned..............................: 69
- Extent Switches..............................: 68
- Avg. Pages per Extent........................: 7.1
- Scan Density [Best Count:Actual Count].......: 88.41% [61:69]
- Extent Scan Fragmentation ...................: 40.58%
- Avg. Bytes Free per Page.....................: 4659.5
- Avg. Page Density (full).....................: 42.43%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

From the number of pages it looks that no more than 61 extents are required. How can I summarize here, any way I can go down on Extent fragmentation & increase avg pages per extent. Alter index & DBCC Indexdefrag wount help.

yup1 Sunday, July 13, 2008 3:57 PM

Really, there is not way around it. To reindex\defrag a table, there has to be a clustered index. Why would not want an clustered index on a table?

JasonMassie Sunday, July 13, 2008 6:03 PM

Just for a tally here you can see the avg pages going down further, DBCC SHOWCONTIG scanning 'Tb_Total' table...

Table: 'Tb_Total' (500196832); index ID: 0, database ID: 9

TABLE level scan performed.

- Pages Scanned................................: 2345

- Extents Scanned..............................: 579

- Extent Switches..............................: 578

- Avg. Pages per Extent........................: 4.1

- Scan Density [Best Count:Actual Count].......: 50.78% [294:579]

- Extent Scan Fragmentation ...................: 26.08%

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

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

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

yup1 Sunday, July 13, 2008 4:08 PM

You need to have a clustered index on the table for the index rebuilds to do anything. Create one and it will fix the problem.

JasonMassie Sunday, July 13, 2008 4:51 PM

Thanks a lot - that did it for me - but just a question to ask here the second listing that you see is for a table with no indexes - now in case I dont require an index here how can I set that ?

yup1 Sunday, July 13, 2008 5:14 PM

Really, there is not way around it. To reindex\defrag a table, there has to be a clustered index. Why would not want an clustered index on a table?

JasonMassie Sunday, July 13, 2008 6:03 PM

You can use google to search for other answers

Custom Search

More Threads

• getting database in recovery exception

• Validate all the columns in a record

• Increase the tempdb size

• One-To-Many Relationship

• SQL Server Managment Studio

• How to troubleshoot when Transaction log is Full?

• the function of theser files

• Stored proc taking more time than query

• Question about index fragmentation information within the sys.dm_db_index_physical_stats dynamic management view

• FullText Indexing