Extent Scan Fragmentation still same after reindexing

  • Please let me explain y my fragmentation is more after reindexing

    and wat can i do more in this case.....

    before reindexing

    DBCC SHOWCONTIG scanning 'table' table...

    Table: 'table' (161695924); index ID: 0, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 15687

    - Extents Scanned..............................: 1978

    - Extent Switches..............................: 1977

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

    - Scan Density [Best Count:Actual Count].......: 99.14% [1961:1978]

    - Extent Scan Fragmentation ...................: 48.33%

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

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

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

    After reindex and dbcc defragindex there is no custer index on this table

    and it is frequently accessed

    DBCC SHOWCONTIG scanning 'table' table...

    Table: 'table' (161695924); index ID: 0, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 15789

    - Extents Scanned..............................: 1992

    - Extent Switches..............................: 1991

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

    - Scan Density [Best Count:Actual Count].......: 99.10% [1974:1992]

    - Extent Scan Fragmentation ...................: 48.64%

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

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

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

  • I'm having similar issues with my system. After first noticing high levels of index fragmentation, I decided to run a job over the weekend and specifically rebuild a number of indexes. My commands look like this:

    ALTER INDEX IX_900_PortfolioSummary__PPCID_PortID ON dbo.[900_PortfolioSummary] REBUILD

    To my surprise, the indexes in question remain just as fragmented as they were before the job. Can anyone advise me why this is so? I thought that a REBUILD completely rebuilt the index with no fragmentation.

    Thanks,

    Arthur

    Arthur Fuller
    cell: 647-710-1314

    Only two businesses refer to their clients as users: drug-dealing and software development.
    -- Arthur Fuller

  • Do you have multiple data Files or so you have partitioning of any kind.

  • can you also do a DBCC CHECKALLOC and post the results.

  • Here is a good old article check ths out:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJKAC

  • Here's the tail of the DBCC CHECKALLOC results.

    (number of mixed extents = 102, mixed pages = 777) in this database.

    CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'BEST'.

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

    Arthur

    Arthur Fuller
    cell: 647-710-1314

    Only two businesses refer to their clients as users: drug-dealing and software development.
    -- Arthur Fuller

  • Oops i missed on a value in youe opening post.

    This suggeste you have a heap table. Right? a table without indexes.

    So a Heap table can be more fragmented under lots on INSERTS/UPDATES/DELETES.

    So some suggestions sould be as Follows:

    you can create a clustered index on the table.

    Or create a new table and insert the data from the heap table into the new table based on some sort order

    Other Option will be to export the data to another table, truncate the table and import the data back into the table

  • i dnt hav an heap table there are non clustered index ....... waiting for a good reply .....

  • CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'abc'.

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

    AFTER CHECKALLOC

    DBCC SHOWCONTIG scanning 'TABLE' table...

    Table: 'TABLE' (161695924); index ID: 0, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 15794

    - Extents Scanned..............................: 1993

    - Extent Switches..............................: 1992

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

    - Scan Density [Best Count:Actual Count].......: 99.10% [1975:1993]

    - Extent Scan Fragmentation ...................: 48.67%

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

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

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

    Plzzz reply wat exctly need to be done as i there is no error found and the table is not an heap table.... it is same as it was....

  • maruf24 (6/23/2008)


    CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'abc'.

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

    AFTER CHECKALLOC

    DBCC SHOWCONTIG scanning 'TABLE' table...

    Table: 'TABLE' (161695924); index ID: 0, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 15794

    - Extents Scanned..............................: 1993

    - Extent Switches..............................: 1992

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

    - Scan Density [Best Count:Actual Count].......: 99.10% [1975:1993]

    - Extent Scan Fragmentation ...................: 48.67%

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

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

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

    Plzzz reply wat exctly need to be done as i there is no error found and the table is not an heap table.... it is same as it was....

    This table does not have a clustered index. An index id of 0 tells us that this is a HEAP. If it was the clustered index - it would be index id 1. Non clustered indexes will have an id of 2 through 255.

    To defragment this table, you have two choices:

    1) Add a clustered index

    2) Create a new table (with a clustered index) and move the data to the new table

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • DBCC SHOWCONTIG scanning 'table' table...

    Table: 'DEVICE_USER_LINK' (161695924); index ID: 1, database ID: 5

    TABLE level scan performed.

    - Pages Scanned................................: 15794

    - Extents Scanned..............................: 1993

    - Extent Switches..............................: 1992

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

    - Scan Density [Best Count:Actual Count].......: 99.10% [1975:1993]

    - Extent Scan Fragmentation ...................: 48.67%

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

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

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

    I hav created the clustered index but the result is the same

  • i hav created the clustered index but the result is same as it was...

  • How much free space do you have available in the database?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff TB free space .... As there was no cluster index wer present in the table and the table were containing non cluster indexes ..wat i did.........

    ...........created a clustered index and doped the non cluster and recreated the non clusterd indexes .... then there was a chng in my fragmentation level..

    One more thing is that i have created the cluster index on the col which contains duplicate records ... as there was a non clusterd index present on tht col and the users wer complaining abut the slow retrieval of data ... as i hav created a cluster index and dropped the non Cluster ... there was a performance boost .........

    Thkz every one ...

  • any more suggestions ..... 🙂

Viewing 15 posts - 1 through 15 (of 15 total)

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