October 18, 2004 at 11:57 am
Hi all,
A few weeks ago I implemented a job to run a DBReindex command on a huge table in a production database (Size of db = 30 Gb)
After that, I had to run a DBCC SHRINKDATABASE since the production database grew considerably. (more than 8 Gb)
Comparing the DBCC SHOWCONTIG outputs before and after both processes were ran I found that the logical fragmentation is worse now than before
Before:
DBCC SHOWCONTIG scanning 'X02' table...
Table: 'X02' (692197516); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 533605
- Extents Scanned..............................: 67270
- Extent Switches..............................: 189249
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 35.24% [66701:189250]
- Logical Scan Fragmentation ..................: 61.19%
- Extent Scan Fragmentation ...................: 20.34%
- Avg. Bytes Free per Page.....................: 2047.8
- Avg. Page Density (full).....................: 74.70%
After:
DBCC SHOWCONTIG scanning 'X02' table...
Table: 'X02' (692197516); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 492106
- Extents Scanned..............................: 61811
- Extent Switches..............................: 98168
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 62.66% [61514:98169]
- Logical Scan Fragmentation ..................: 99.97%
- Extent Scan Fragmentation ...................: 3.59%
- Avg. Bytes Free per Page.....................: 1523.9
- Avg. Page Density (full).....................: 81.17%
According to some posts I´ve been reading, it seems that DBCC SHRINKDATABASE is responsible for this (but running it is necessary)
What can I do ? Is there any other way to reduce fragmentation ? NOTE: It's SQL 7.0 (!)
Thank you in advance,
October 18, 2004 at 1:29 pm
run the shrink first with datapage move then run the reindex. I'm guessing that is what is causing the issues is the DBCC moving datapages to make the shrink happen.
Wes
October 19, 2004 at 5:34 am
Yes, I think that DBCC ShrinkDatabase is fragmenting the indexes.
But the problem is that I can't let the database grow, so is useless to shrink the db first, and run Dbreindex because I would have to run Dbcc Shrink again after it.
Any ideas ?
October 21, 2004 at 10:00 am
assuming you leave a reasonable amount of free space in your mdf file the shrink shouldn't fragment the tables - however you may find it beneficial to reindex all your tables ( that are fragmented ) . To be honest if I'm doing a serious re-index on a db I normally put it into simple recovery model first - this seems to usually work better.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply