index defrag

Dear friends,
I just defrag my index, this is the results.
Before defrag
--
DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
Table: 'tbSOTransDetail' (1765581328); index ID: 3,
database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 151
- Extents Scanned.......................: 20
- Extent Switches.......................: 147
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 12.84%
[19:148]
- Logical Scan Fragmentation ..............: 49.01%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 2748.8
- Avg. Page Density (full)................: 66.04%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
page scanned pages moved pages removed
146 104 40
After defrag
--
DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
Table: 'tbSOTransDetail' (1765581328); index ID: 3,
database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 111
- Extents Scanned.......................: 16
- Extent Switches.......................: 22
- Avg. Pages per Extent..................: 6.9
- Scan Density [Best Count:Actual Count]......: 60.87%
[14:23]
- Logical Scan Fragmentation ..............: 4.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 821.8
- Avg. Page Density (full)................: 89.85%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
My questions are,
1. can we make logical scan fragmentation equal with 0 ?
2. I got 75% extent scan fragmentation , is it bad ? why
is the number of extent scan fragmentation still 75 after
I defrag it ?
3. Can we make scan density value [Best Count:Actual
Count] equal with 100 % ?
Thanks.Hi Kresna
DBCC INDEXDEFRAG will only remove logical scan fragmentation and getting it
down to 4% is pretty good. The only way to get scan density up to 100% and
extent scan fragmentation down is to actually rebuild the clustered index.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
news:034a01c39c3a$939c5c00$a601280a@.phx.gbl...
> Dear friends,
> I just defrag my index, this is the results.
> Before defrag
> --
> DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 151
> - Extents Scanned.......................: 20
> - Extent Switches.......................: 147
> - Avg. Pages per Extent..................: 7.5
> - Scan Density [Best Count:Actual Count]......: 12.84%
> [19:148]
> - Logical Scan Fragmentation ..............: 49.01%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 2748.8
> - Avg. Page Density (full)................: 66.04%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> page scanned pages moved pages removed
> 146 104 40
> After defrag
> --
> DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 111
> - Extents Scanned.......................: 16
> - Extent Switches.......................: 22
> - Avg. Pages per Extent..................: 6.9
> - Scan Density [Best Count:Actual Count]......: 60.87%
> [14:23]
> - Logical Scan Fragmentation ..............: 4.50%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 821.8
> - Avg. Page Density (full)................: 89.85%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> My questions are,
> 1. can we make logical scan fragmentation equal with 0 ?
> 2. I got 75% extent scan fragmentation , is it bad ? why
> is the number of extent scan fragmentation still 75 after
> I defrag it ?
> 3. Can we make scan density value [Best Count:Actual
> Count] equal with 100 % ?
>
> Thanks.
>
>|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
For a table with only 151 pages I wouldn't even bother worrying about
fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueKQctEnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi Kresna
> DBCC INDEXDEFRAG will only remove logical scan fragmentation and getting
it
> down to 4% is pretty good. The only way to get scan density up to 100% and
> extent scan fragmentation down is to actually rebuild the clustered index.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kresna rudy kurniawan" <kresna_rk@.hotmail.com> wrote in message
> news:034a01c39c3a$939c5c00$a601280a@.phx.gbl...
> > Dear friends,
> >
> > I just defrag my index, this is the results.
> >
> > Before defrag
> > --
> > DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> > Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> > database ID: 7
> > LEAF level scan performed.
> > - Pages Scanned........................: 151
> > - Extents Scanned.......................: 20
> > - Extent Switches.......................: 147
> > - Avg. Pages per Extent..................: 7.5
> > - Scan Density [Best Count:Actual Count]......: 12.84%
> > [19:148]
> > - Logical Scan Fragmentation ..............: 49.01%
> > - Extent Scan Fragmentation ...............: 75.00%
> > - Avg. Bytes Free per Page................: 2748.8
> > - Avg. Page Density (full)................: 66.04%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> >
> >
> > page scanned pages moved pages removed
> >
> > 146 104 40
> >
> > After defrag
> > --
> >
> > DBCC SHOWCONTIG scanning 'tbSOTransDetail' table...
> > Table: 'tbSOTransDetail' (1765581328); index ID: 3,
> > database ID: 7
> > LEAF level scan performed.
> > - Pages Scanned........................: 111
> > - Extents Scanned.......................: 16
> > - Extent Switches.......................: 22
> > - Avg. Pages per Extent..................: 6.9
> > - Scan Density [Best Count:Actual Count]......: 60.87%
> > [14:23]
> > - Logical Scan Fragmentation ..............: 4.50%
> > - Extent Scan Fragmentation ...............: 75.00%
> > - Avg. Bytes Free per Page................: 821.8
> > - Avg. Page Density (full)................: 89.85%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> >
> > My questions are,
> > 1. can we make logical scan fragmentation equal with 0 ?
> > 2. I got 75% extent scan fragmentation , is it bad ? why
> > is the number of extent scan fragmentation still 75 after
> > I defrag it ?
> > 3. Can we make scan density value [Best Count:Actual
> > Count] equal with 100 % ?
> >
> >
> > Thanks.
> >
> >
> >
>