Index Monitoring

I monitor the index fragmentation through dbcc showcontig command , i get result as
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).

But i want to know can we get value of 'extent switches' and logical scan fragmentation' through system tables , because we know that all database details are updated in system tables.As you have posted a question in the article section it ts being moved to SQL Server Forum.

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below. The second line is a valid index - cognos_1 - but the first line doesn't have an index name but it shows 90% fragmentation. Why does that line without an index name show up? Is there a way to get rid of it? Does it matter that it shows up?
d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2 NULL NULL 33.333333333333329 1 3 0.0 NULL
Thanks,
--
Dan D.It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below. The second line is a valid index - cognos_1 - but the first line doesn't have an index name but it show
s 90% fragmentation. Why does that line without an index name show up? Is there a way to get rid of it? Does it matter that it shows up?
d_promotion_date45357665400101132911268.37099999999999521454129.8999938964843798.39511108398437540.02590.080.0
d_promotion_date453576654cognos_1608NULLNULLNULLNULLNULL02NULLNULL33.333333333333329130.0NULL
Thanks,
Dan D.
It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.
|||Thanks.
Dan D.
"Paul S Randal [MS]" wrote:

> It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
> null name and index id=0. It also explains that logical scan fragmentation
> is not relevant for heaps.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
> The second line is a valid index - cognos_1 - but the first line doesn't
> have an index name but it shows 90% fragmentation. Why does that line
> without an index name show up? Is there a way to get rid of it? Does it
> matter that it shows up?
> 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> NULL NULL 33.333333333333329 1 3 0.0 NULL
>
>

index fragmentation

Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH F
AST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't hav
e an index name but it show
s 90% fragmentation. Why does that line without an index name show up? Is th
ere a way to get rid of it? Does it matter that it shows up?
d_promotion_date 453576654 0 0 10 1132
9 112 68.370999999999995 214 5 4 129
.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
d_promotion_date 453576654 cognos_1 6 0
8 NULL NULL NULL NULL NULL 0 2 NULL
NULL 33.333333333333329 1 3 0.0 NULL
Thanks,
--
Dan D.It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
null name and index id=0. It also explains that logical scan fragmentation
is not relevant for heaps.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> Using SS2000. Sometimes when I run DBCC SHOWCONTIG (D_Promotion_Date) WITH
FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those below.
The second line is a valid index - cognos_1 - but the first line doesn't
have an index name but it shows 90% fragmentation. Why does that line
without an index name show up? Is there a way to get rid of it? Does it
matter that it shows up?
>
> d_promotion_date 453576654 0 0 10 1132 9 112 68.370999999999995 214 5 4
129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> d_promotion_date 453576654 cognos_1 6 0 8 NULL NULL NULL NULL NULL 0 2
NULL NULL 33.333333333333329 1 3 0.0 NULL
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Paul S Randal [MS]" wrote:

> It's the heap - look in BOL for DBCC SHOWCONTIG and it explains about the
> null name and index id=0. It also explains that logical scan fragmentation
> is not relevant for heaps.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E5B09B34-D54E-4176-BE47-41B41E6D95E3@.microsoft.com...
> FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS I get results like those belo
w.
> The second line is a valid index - cognos_1 - but the first line doesn't
> have an index name but it shows 90% fragmentation. Why does that line
> without an index name show up? Is there a way to get rid of it? Does it
> matter that it shows up?
> 129.89999389648437 98.395111083984375 40.0 2 5 90.0 80.0
> NULL NULL 33.333333333333329 1 3 0.0 NULL
>
>

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.
> >
> >
> >
>