June 23, 2008 at 12:55 am
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.
June 23, 2008 at 5:24 am
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
June 23, 2008 at 7:06 am
Do you have multiple data Files or so you have partitioning of any kind.
Maninder
www.dbanation.com
June 23, 2008 at 7:09 am
can you also do a DBCC CHECKALLOC and post the results.
Maninder
www.dbanation.com
June 23, 2008 at 7:11 am
Here is a good old article check ths out:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EJKAC
Maninder
www.dbanation.com
June 23, 2008 at 8:21 am
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
June 23, 2008 at 10:36 am
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
Maninder
www.dbanation.com
June 23, 2008 at 10:19 pm
i dnt hav an heap table there are non clustered index ....... waiting for a good reply .....
June 23, 2008 at 10:34 pm
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....
June 23, 2008 at 10:50 pm
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
June 23, 2008 at 11:23 pm
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
June 23, 2008 at 11:25 pm
i hav created the clustered index but the result is same as it was...
June 23, 2008 at 11:37 pm
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
June 26, 2008 at 9:37 pm
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 ...
July 3, 2008 at 7:41 am
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