Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
Thanx
It's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Showing posts with label scan. Show all posts
Showing posts with label scan. Show all posts
Friday, March 9, 2012
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=
0
Logical Scan Fragmentation is not relavent for heaps
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Logical Scan Fragmentation..issue!
Hello,
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
all tables on my database to see what is condition of index fragmentation.
For an example here is on of those tables:
DBCC SHOWCONTIG scanning 'studenti' table...
Table: 'studenti' (1635536910); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 63
- Extents Scanned.......................: 11
- Extent Switches.......................: 10
- Avg. Pages per Extent..................: 5.7
- Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
- Extent Scan Fragmentation ...............: 36.36%
- Avg. Bytes Free per Page................: 492.7
- Avg. Page Density (full)................: 93.91%
It's not big table..so far 1209 rows.
If you noticed there is not value for Logical Scan Fragmentation? All my
tables in database have that value (45 od them) and just 4 of them not.
Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
doesn't change and of values in exemple above.
Other tables, with Logical Scan Fragmentation values shown, are ok and DBCC
INDEXDRAG do just fine..as expected.
What are possible reasons for this issue or problem!
ThanxIt's because the table is a heap (has no clustered index). You can tell
because the index ID is 0. If you check the other tables you should see that
you don't get Logical Scan Fragmentation for any heaps. Here's a quick query
that will determine tables with no clustered index
select [name] from sysobjects
where objectproperty(id,'IsUserTable')=1
and objectproperty(id,'TableHasClustIndex')=0
Logical Scan Fragmentation is not relavent for heaps
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jasmin" <Jasmin@.discussions.microsoft.com> wrote in message
news:F729DB47-DA92-4319-AC14-7BADBE5AC17E@.microsoft.com...
> Hello,
> I have some issue or problems I dont know?. I have tray DBCC SHOWCONTIG on
> all tables on my database to see what is condition of index fragmentation.
> For an example here is on of those tables:
> DBCC SHOWCONTIG scanning 'studenti' table...
> Table: 'studenti' (1635536910); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 63
> - Extents Scanned.......................: 11
> - Extent Switches.......................: 10
> - Avg. Pages per Extent..................: 5.7
> - Scan Density [Best Count:Actual Count]......: 72.73% [8:11]
> - Extent Scan Fragmentation ...............: 36.36%
> - Avg. Bytes Free per Page................: 492.7
> - Avg. Page Density (full)................: 93.91%
> It's not big table..so far 1209 rows.
> If you noticed there is not value for Logical Scan Fragmentation? All my
> tables in database have that value (45 od them) and just 4 of them not.
> Executin command DBCC INDEXDRAG on that 4 tables, with any fill factor,
> doesn't change and of values in exemple above.
> Other tables, with Logical Scan Fragmentation values shown, are ok and
> DBCC
> INDEXDRAG do just fine..as expected.
> What are possible reasons for this issue or problem!
> Thanx
Logical Scan fragmentation ?
Can someone explain this statement ?
"An out of order page is one for which the next page indicated in an IAM is
a different page than the page pointed to by the next page pointer in the
leaf page."
Does this mean that DBCC checktable may result in some allocation errors ?Hi Hassan
No, this is not an error. The IAM indicates the next physical page and the
pointers indicate the next logical page. It just means that the a page is
out of order. :-)
So let's say your table is on page 82, 86 and 95. The IAM would indicate
them in that physical order. But it may be that the first rows in the index
are on page 95 (for example, valus A-G) then the next logical values are on
page 86 (values H - S) and the next are on page 82 (values T - Z). When you
are on page 86, the pointers will say that the next page is 82, but the IAM
will say that the next page is 95. This is just fragmentation, not an
error.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>|||Read the whitepaper at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which should explain everything for you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>
"An out of order page is one for which the next page indicated in an IAM is
a different page than the page pointed to by the next page pointer in the
leaf page."
Does this mean that DBCC checktable may result in some allocation errors ?Hi Hassan
No, this is not an error. The IAM indicates the next physical page and the
pointers indicate the next logical page. It just means that the a page is
out of order. :-)
So let's say your table is on page 82, 86 and 95. The IAM would indicate
them in that physical order. But it may be that the first rows in the index
are on page 95 (for example, valus A-G) then the next logical values are on
page 86 (values H - S) and the next are on page 82 (values T - Z). When you
are on page 86, the pointers will say that the next page is 82, but the IAM
will say that the next page is 95. This is just fragmentation, not an
error.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>|||Read the whitepaper at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which should explain everything for you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>
Friday, February 24, 2012
Logical
I would like to re-index two tables (t1 and t2) if the logical scan
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,
Subscribe to:
Posts (Atom)