DBCC SHOWCONTIG shows no result

Hello
Trying to get some fragmentation info back on SQL2000 tables but no results
are returned. Is this a bug?
DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
DBCC SHOWCONTIG ('MsgAudit_CBOT')
DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
-- all above tables exist and have clustered index with millions of rows.
tks
all queries produce no result:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
-- cranfield, DBAAre you certain you really execute the queries? Press the "execute" button? Also, can you try using
OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> Hello
> Trying to get some fragmentation info back on SQL2000 tables but no results
> are returned. Is this a bug?
> DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> ALL_INDEXES, NO_INFOMSGS
> DBCC SHOWCONTIG ('MsgAudit_CBOT')
> DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> -- all above tables exist and have clustered index with millions of rows.
> tks
>
> all queries produce no result:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA|||I am certain query is executed. Have also tried osql - still no results, just
as below. My SQL2005 table returns results but NOT SQL2000
1> use eqmclog
2> go
1> dbcc showcontig('providersourceinfo')
2> go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
1>
very strange this...
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Are you certain you really execute the queries? Press the "execute" button? Also, can you try using
> OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> > Hello
> >
> > Trying to get some fragmentation info back on SQL2000 tables but no results
> > are returned. Is this a bug?
> >
> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> > ALL_INDEXES, NO_INFOMSGS
> >
> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >
> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >
> > -- all above tables exist and have clustered index with millions of rows.
> >
> > tks
> >
> >
> > all queries produce no result:
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
>|||What happens if you omit the single quotes? As in DBCC SHOWCONTIG
(MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
2000 BOL, it does not indicate the use of single quotes around the object
name, but does in the SQL Server 2005 topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>I am certain query is executed. Have also tried osql - still no results,
>just
> as below. My SQL2005 table returns results but NOT SQL2000
> 1> use eqmclog
> 2> go
> 1> dbcc showcontig('providersourceinfo')
> 2> go
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> 1>
> very strange this...
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Are you certain you really execute the queries? Press the "execute"
>> button? Also, can you try using
>> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> > Hello
>> >
>> > Trying to get some fragmentation info back on SQL2000 tables but no
>> > results
>> > are returned. Is this a bug?
>> >
>> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> > ALL_INDEXES, NO_INFOMSGS
>> >
>> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >
>> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >
>> > -- all above tables exist and have clustered index with millions of
>> > rows.
>> >
>> > tks
>> >
>> >
>> > all queries produce no result:
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA|||no, omitting quotes makes no difference. MS uses quotes themselves in their
BOL samples.
Just to make sure I have the correct tables:
select 'DBCC SHOWCONTIG ('+name+')'
from sysobjects
where type = 'u'
DBCC SHOWCONTIG (DataRecoveryLog)
DBCC SHOWCONTIG (ProviderSourceInfo)
DBCC SHOWCONTIG (MsgAudit_CBOT)
DBCC SHOWCONTIG (MsgAudit_KCBT)
DBCC SHOWCONTIG (MsgAudit_MGEX)
DBCC SHOWCONTIG (MsgAudit_WCE)
DBCC SHOWCONTIG (MsgAudit_JADE)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
-- cranfield, DBA
"Gail Erickson [MS]" wrote:
> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> 2000 BOL, it does not indicate the use of single quotes around the object
> name, but does in the SQL Server 2005 topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >I am certain query is executed. Have also tried osql - still no results,
> >just
> > as below. My SQL2005 table returns results but NOT SQL2000
> >
> > 1> use eqmclog
> > 2> go
> > 1> dbcc showcontig('providersourceinfo')
> > 2> go
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > 1>
> >
> > very strange this...
> > --
> > -- cranfield, DBA
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you certain you really execute the queries? Press the "execute"
> >> button? Also, can you try using
> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> exist.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> > Hello
> >> >
> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> > results
> >> > are returned. Is this a bug?
> >> >
> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> > ALL_INDEXES, NO_INFOMSGS
> >> >
> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >
> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >
> >> > -- all above tables exist and have clustered index with millions of
> >> > rows.
> >> >
> >> > tks
> >> >
> >> >
> >> > all queries produce no result:
> >> >
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >> >
> >> >
> >> > --
> >> > -- cranfield, DBA
> >>
>
>|||Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
TABLERESULTS, you get text back (use the correct tab in QA results).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> no, omitting quotes makes no difference. MS uses quotes themselves in their
> BOL samples.
> Just to make sure I have the correct tables:
> select 'DBCC SHOWCONTIG ('+name+')'
> from sysobjects
> where type = 'u'
> DBCC SHOWCONTIG (DataRecoveryLog)
> DBCC SHOWCONTIG (ProviderSourceInfo)
> DBCC SHOWCONTIG (MsgAudit_CBOT)
> DBCC SHOWCONTIG (MsgAudit_KCBT)
> DBCC SHOWCONTIG (MsgAudit_MGEX)
> DBCC SHOWCONTIG (MsgAudit_WCE)
> DBCC SHOWCONTIG (MsgAudit_JADE)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA
>
> "Gail Erickson [MS]" wrote:
>> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> 2000 BOL, it does not indicate the use of single quotes around the object
>> name, but does in the SQL Server 2005 topics.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >I am certain query is executed. Have also tried osql - still no results,
>> >just
>> > as below. My SQL2005 table returns results but NOT SQL2000
>> >
>> > 1> use eqmclog
>> > 2> go
>> > 1> dbcc showcontig('providersourceinfo')
>> > 2> go
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > 1>
>> >
>> > very strange this...
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you certain you really execute the queries? Press the "execute"
>> >> button? Also, can you try using
>> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> exist.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> > Hello
>> >> >
>> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> > results
>> >> > are returned. Is this a bug?
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >
>> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >
>> >> > -- all above tables exist and have clustered index with millions of
>> >> > rows.
>> >> >
>> >> > tks
>> >> >
>> >> >
>> >> > all queries produce no result:
>> >> >
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > -- cranfield, DBA
>> >>
>>|||Hi Tibor
I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
not return results for empty tables.
doh!
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
> TABLERESULTS, you get text back (use the correct tab in QA results).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> > no, omitting quotes makes no difference. MS uses quotes themselves in their
> > BOL samples.
> >
> > Just to make sure I have the correct tables:
> >
> > select 'DBCC SHOWCONTIG ('+name+')'
> > from sysobjects
> > where type = 'u'
> >
> > DBCC SHOWCONTIG (DataRecoveryLog)
> > DBCC SHOWCONTIG (ProviderSourceInfo)
> > DBCC SHOWCONTIG (MsgAudit_CBOT)
> > DBCC SHOWCONTIG (MsgAudit_KCBT)
> > DBCC SHOWCONTIG (MsgAudit_MGEX)
> > DBCC SHOWCONTIG (MsgAudit_WCE)
> > DBCC SHOWCONTIG (MsgAudit_JADE)
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> >> 2000 BOL, it does not indicate the use of single quotes around the object
> >> name, but does in the SQL Server 2005 topics.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no rights
> >> Download the latest version of Books Online from
> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >> >I am certain query is executed. Have also tried osql - still no results,
> >> >just
> >> > as below. My SQL2005 table returns results but NOT SQL2000
> >> >
> >> > 1> use eqmclog
> >> > 2> go
> >> > 1> dbcc showcontig('providersourceinfo')
> >> > 2> go
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> > 1>
> >> >
> >> > very strange this...
> >> > --
> >> > -- cranfield, DBA
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you certain you really execute the queries? Press the "execute"
> >> >> button? Also, can you try using
> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> >> exist.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> >> > Hello
> >> >> >
> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> >> > results
> >> >> > are returned. Is this a bug?
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> >> > ALL_INDEXES, NO_INFOMSGS
> >> >> >
> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >> >
> >> >> > -- all above tables exist and have clustered index with millions of
> >> >> > rows.
> >> >> >
> >> >> > tks
> >> >> >
> >> >> >
> >> >> > all queries produce no result:
> >> >> >
> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> >> > system administrator.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > -- cranfield, DBA
> >> >>
> >>
> >>
> >>
>|||Ouch... That was not obvious! Thanks for posting back. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:FF387413-A310-4994-8A65-B118987CBF42@.microsoft.com...
> Hi Tibor
> I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
> not return results for empty tables.
> doh!
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
>> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
>> TABLERESULTS, you get text back (use the correct tab in QA results).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
>> > no, omitting quotes makes no difference. MS uses quotes themselves in their
>> > BOL samples.
>> >
>> > Just to make sure I have the correct tables:
>> >
>> > select 'DBCC SHOWCONTIG ('+name+')'
>> > from sysobjects
>> > where type = 'u'
>> >
>> > DBCC SHOWCONTIG (DataRecoveryLog)
>> > DBCC SHOWCONTIG (ProviderSourceInfo)
>> > DBCC SHOWCONTIG (MsgAudit_CBOT)
>> > DBCC SHOWCONTIG (MsgAudit_KCBT)
>> > DBCC SHOWCONTIG (MsgAudit_MGEX)
>> > DBCC SHOWCONTIG (MsgAudit_WCE)
>> > DBCC SHOWCONTIG (MsgAudit_JADE)
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Gail Erickson [MS]" wrote:
>> >
>> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> >> 2000 BOL, it does not indicate the use of single quotes around the object
>> >> name, but does in the SQL Server 2005 topics.
>> >>
>> >> --
>> >> Gail Erickson [MS]
>> >> SQL Server Documentation Team
>> >> This posting is provided "AS IS" with no warranties, and confers no rights
>> >> Download the latest version of Books Online from
>> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >> >I am certain query is executed. Have also tried osql - still no results,
>> >> >just
>> >> > as below. My SQL2005 table returns results but NOT SQL2000
>> >> >
>> >> > 1> use eqmclog
>> >> > 2> go
>> >> > 1> dbcc showcontig('providersourceinfo')
>> >> > 2> go
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> > 1>
>> >> >
>> >> > very strange this...
>> >> > --
>> >> > -- cranfield, DBA
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Are you certain you really execute the queries? Press the "execute"
>> >> >> button? Also, can you try using
>> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> >> exist.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> >> > Hello
>> >> >> >
>> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> >> > results
>> >> >> > are returned. Is this a bug?
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >> >
>> >> >> > -- all above tables exist and have clustered index with millions of
>> >> >> > rows.
>> >> >> >
>> >> >> > tks
>> >> >> >
>> >> >> >
>> >> >> > all queries produce no result:
>> >> >> >
>> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> >> > system administrator.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > -- cranfield, DBA
>> >> >>
>> >>
>> >>
>> >>

DBCC SHOWCONTIG Questions

On most of the results from a series of DBCC SHOWCONTIG
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?
Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?
|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev 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:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent[vbcol=seagreen]
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev 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:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I[vbcol=seagreen]
>

DBCC Showcontig and extent fragmentation

I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev 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:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
> >I have a large table that I cannot seem to bring the extent fragmentation
> > down to an aceptable level. Fist the logical scan density and logical
> > fragmentation were extreamly high so I rebuit the index. This helped
both
> > the logical scan density and logical fragmentation but the extent
> > fragmentation stayed at 65%. I then did an index defrag and the extent
> > fragmentation stayed at 65%. I then Reindexed again and the extent
> > fragmentation dropped to 33%. I defraged and reindexed once more and it
> > droped to 21%. I could not get this number to drop any more from 21% so
I
> > droped the index and recreated it; - and it came back with an extent
> > fragmentation of 34%. Is there a way, short of moving to another file
> > group
> > to get my extent fragmentation down without jumping through all these
> > hoops?
>

dbcc showcontig

When I run command: dbcc showcontig with all_indexes, I got a lot of indexes w/ high logical fragmentation
So I ran dbcc dbreindex on all the indexes
When I re-ran showcontig to verify each table individually, it reported 0% frag per index
For one last time, I ran a full report of the entire database, the logicalfrag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_indexes have different results for logicalfrag100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages).
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read the
> whitepaper below for full details:
> http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
> > 100% fragmentation is only possible with tiny indexes (i.e. several
pages).
> > Can you give some examples of the output you're comparing?
> >
> > You don't need to fix fragmentation on all indexes - it depends on your
> > access patterns whether fragmentation will affect your performance. Read
the
> > whitepaper below for full details:
> >
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > Regards.
> >
>

dbcc showcontig

When I run command: dbcc showcontig with all_indexes, I got a lot of indexe
s w/ high logical fragmentation.
So I ran dbcc dbreindex on all the indexes.
When I re-ran showcontig to verify each table individually, it reported 0% f
rag per index.
For one last time, I ran a full report of the entire database, the logicalfr
ag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_inde
xes have different results for logicalfrag?100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages)
.
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read t
he
> whitepaper below for full details:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
pages).
the
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
>

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
..obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
Olu Adedeji
Do you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>

DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>

DBCC DBREINDEX question and problem

I have really bad database fragmentation, in some cases up to 95% fragmented. I continue to run DBCC DBREINDEX on these tables to try and fix this problem but from some reason, no matter how often I do it, I never see an increase in Scan Density. The tables in question do have more than 8 pages so I know that is not the issue. Anyone have any insight on this?

http://www.sql-server-performance.com/rd_index_fragmentation.asp

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

The above links should help you to understand the features available in SQL Server and resolve the defragmentation problems.

DBCC DBReindex on NonClustered

If I execute the following statement on a nonclustered index there is no
reduction in fragmentation on the index:
DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
NO_INFOMSGS
The table on which I am performing the above statement has a clustered index
.
Is that the reason the above DBCC DBReindex would not reduce fragmentation
using the above statement? In other words, if a table has a clustered index,
then in order to reduce fragmentation on a nonclustered index in the same
table that has the clustered index, I would need to perform a DROP and CREAT
E
INDEX on the nonclustered index?
Message posted via http://www.droptable.comcbrichards via droptable.com wrote:
> If I execute the following statement on a nonclustered index there is no
> reduction in fragmentation on the index:
> DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
> NO_INFOMSGS
> The table on which I am performing the above statement has a clustered ind
ex.
> Is that the reason the above DBCC DBReindex would not reduce fragmentation
> using the above statement? In other words, if a table has a clustered inde
x,
> then in order to reduce fragmentation on a nonclustered index in the same
> table that has the clustered index, I would need to perform a DROP and CRE
ATE
> INDEX on the nonclustered index?
>
Please post the output of DBCC SHOWCONTIG for this index...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (862501003); index ID: 43, database ID: 61
LEAF level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 85.71%
- Extent Scan Fragmentation ...............: 42.86%
- Avg. Bytes Free per Page................: 488.7
- Avg. Page Density (full)................: 93.96%
DBCC execution completed. If DBCC printed error messages, contact your syste
m
administrator.
Tracy McKibben wrote:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||Since your last reply I have performed the following operations:
I have dropped and recreated this nonclustered index, and there is no
difference in fragmentation.
I have dropped and recreated the clustered index, and there is no difference
in fragmentation on the clustered or nonclustered index.
Any ideas?
Tracy McKibben wrote:
>[quoted text clipped - 8 lines]
>Please post the output of DBCC SHOWCONTIG for this index...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||Your index only has 7 pages in it. Anything less than 8 pages will use a
mixed extent. That means it will share as much as 8 extents and will always
show as fragmented. Only after you get more than 8 pages can you make them
all contiguous. But this is pretty moot anyway. I wouldn't worry about
anything with such few pages as I am sure there are more pressing issues
that you can address. I usually don't pay much attention to indexes with
less than several hundred pages at least.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64eb85d653ff4@.uwe...
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (862501003); index ID: 43, database ID: 61
> LEAF level scan performed.
> - Pages Scanned........................: 7
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
> - Logical Scan Fragmentation ..............: 85.71%
> - Extent Scan Fragmentation ...............: 42.86%
> - Avg. Bytes Free per Page................: 488.7
> - Avg. Page Density (full)................: 93.96%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
> administrator.
> Tracy McKibben wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>

DBCC DBReindex on NonClustered

If I execute the following statement on a nonclustered index there is no
reduction in fragmentation on the index:
DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
NO_INFOMSGS
The table on which I am performing the above statement has a clustered index.
Is that the reason the above DBCC DBReindex would not reduce fragmentation
using the above statement? In other words, if a table has a clustered index,
then in order to reduce fragmentation on a nonclustered index in the same
table that has the clustered index, I would need to perform a DROP and CREATE
INDEX on the nonclustered index?
--
Message posted via http://www.sqlmonster.comcbrichards via SQLMonster.com wrote:
> If I execute the following statement on a nonclustered index there is no
> reduction in fragmentation on the index:
> DBCC DBREINDEX ('DBName.dbo.MyTable','IDX_MyTable_NonClustered',0) WITH
> NO_INFOMSGS
> The table on which I am performing the above statement has a clustered index.
> Is that the reason the above DBCC DBReindex would not reduce fragmentation
> using the above statement? In other words, if a table has a clustered index,
> then in order to reduce fragmentation on a nonclustered index in the same
> table that has the clustered index, I would need to perform a DROP and CREATE
> INDEX on the nonclustered index?
>
Please post the output of DBCC SHOWCONTIG for this index...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (862501003); index ID: 43, database ID: 61
LEAF level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 85.71%
- Extent Scan Fragmentation ...............: 42.86%
- Avg. Bytes Free per Page................: 488.7
- Avg. Page Density (full)................: 93.96%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Since your last reply I have performed the following operations:
I have dropped and recreated this nonclustered index, and there is no
difference in fragmentation.
I have dropped and recreated the clustered index, and there is no difference
in fragmentation on the clustered or nonclustered index.
Any ideas?
Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and CREATE
>> INDEX on the nonclustered index?
>Please post the output of DBCC SHOWCONTIG for this index...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||Your index only has 7 pages in it. Anything less than 8 pages will use a
mixed extent. That means it will share as much as 8 extents and will always
show as fragmented. Only after you get more than 8 pages can you make them
all contiguous. But this is pretty moot anyway. I wouldn't worry about
anything with such few pages as I am sure there are more pressing issues
that you can address. I usually don't pay much attention to indexes with
less than several hundred pages at least.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64eb85d653ff4@.uwe...
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (862501003); index ID: 43, database ID: 61
> LEAF level scan performed.
> - Pages Scanned........................: 7
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
> - Logical Scan Fragmentation ..............: 85.71%
> - Extent Scan Fragmentation ...............: 42.86%
> - Avg. Bytes Free per Page................: 488.7
> - Avg. Page Density (full)................: 93.96%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
> administrator.
> Tracy McKibben wrote:
>> If I execute the following statement on a nonclustered index there is no
>> reduction in fragmentation on the index:
>>[quoted text clipped - 8 lines]
>> table that has the clustered index, I would need to perform a DROP and
>> CREATE
>> INDEX on the nonclustered index?
>>Please post the output of DBCC SHOWCONTIG for this index...
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
Thx
How big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:

> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>
|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If not
> then you can rebuild til you are blue in the face and it will still stay the
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.
|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).
|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:[vbcol=seagreen]
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>
|||Is it an index or the datapages you want to defrag? There is no way to defrag the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmentation really is for the
data pages. There is no linked list for the data pages in a heap table, so there's no jumping back
and forth. So the type of fragmentation we can talk about is if you either have non-consecutive
extents or less than full pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> I thought it may be index related (hence my mentioning the indexes). The table itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clustered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STATISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX when they have no
> clustered indexes in the DB.
>
>
|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
ThxHow big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:

> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If no
t
> then you can rebuild til you are blue in the face and it will still stay t
he
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective i
n
> removing fragmentation. Create a clustered index and then drop it. This wi
ll
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:[vbcol=seagreen]
> Paul
> You really need a clustered index for reindex or rebuild to be effective i
n
> removing fragmentation. Create a clustered index and then drop it. This wi
ll
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>|||Is it an index or the datapages you want to defrag? There is no way to defra
g the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmenta
tion really is for the
data pages. There is no linked list for the data pages in a heap table, so t
here's no jumping back
and forth. So the type of fragmentation we can talk about is if you either h
ave non-consecutive
extents or less than full pages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx
.gbl...
> Andrew J. Kelly wrote:
> I thought it may be index related (hence my mentioning the indexes). The t
able itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clust
ered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STAT
ISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX w
hen they have no
> clustered indexes in the DB.
>
>|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:

DBCC DBREINDEX not changing anything!

This is quite weird...
Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
rebuild anything in the indexes, and fragmentation remains (67%).
Am I doing something incredibly stupid here or is there an issue I need
to be aware of?
Table has non-clustered indexes on it only.
Any suggestions? Has anyone seen this behaviour before?
ThxHow big is the table? Do you have a clustered index on the table? If not
then you can rebuild til you are blue in the face and it will still stay the
same. One of the reasons why most every table should have a clustered
index.
--
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:ez$QWeOGFHA.444@.TK2MSFTNGP15.phx.gbl...
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need to
> be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx|||Paul
You really need a clustered index for reindex or rebuild to be effective in
removing fragmentation. Create a clustered index and then drop it. This will
reduce your fragmentation, and due to not having a clustered index your
tables will not fragment as quickly afterwards.
Hope this helps
John
"Paul Buxton" wrote:
> This is quite weird...
> Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
> rebuild anything in the indexes, and fragmentation remains (67%).
> Am I doing something incredibly stupid here or is there an issue I need
> to be aware of?
> Table has non-clustered indexes on it only.
> Any suggestions? Has anyone seen this behaviour before?
> Thx
>|||Andrew J. Kelly wrote:
> How big is the table? Do you have a clustered index on the table? If not
> then you can rebuild til you are blue in the face and it will still stay the
> same. One of the reasons why most every table should have a clustered
> index.
>
I thought it may be index related (hence my mentioning the indexes).
The table itself is 1Gb in size. Unfortunately, I can't change the
indexing (MSCRM - and not a clustered index in sight!) as support will
be removed if we do.
Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE
STATISTICS only. Strangely, The MS CRM Performance Tuning Whitepaper
suggests running the DBREINDEX when they have no clustered indexes in
the DB.|||John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
>
Sadly, I can't change the schema for the DB or create indexes as far as
I can tell. My understanding is that we lose our support if we do
(system is MS CRM).|||Sadly, I can't change the database in anyway or we lose support (system
is MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I
can do.
John Bandettini wrote:
> Paul
> You really need a clustered index for reindex or rebuild to be effective in
> removing fragmentation. Create a clustered index and then drop it. This will
> reduce your fragmentation, and due to not having a clustered index your
> tables will not fragment as quickly afterwards.
> Hope this helps
> John
> "Paul Buxton" wrote:
>
>>This is quite weird...
>>Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
>>rebuild anything in the indexes, and fragmentation remains (67%).
>>Am I doing something incredibly stupid here or is there an issue I need
>>to be aware of?
>>Table has non-clustered indexes on it only.
>>Any suggestions? Has anyone seen this behaviour before?
>>Thx|||Is it an index or the datapages you want to defrag? There is no way to defrag the data pages for a
table without a clustered index (heap table) with less than:
1 Export, truncate, import (or similar)
or
2 Create a clustered index and then drop it.
Also, if this is indeed a heap table, you need to think about that fragmentation really is for the
data pages. There is no linked list for the data pages in a heap table, so there's no jumping back
and forth. So the type of fragmentation we can talk about is if you either have non-consecutive
extents or less than full pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul Buxton" <spireite@.hotmail.com> wrote in message news:Oi4RdgQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
>> How big is the table? Do you have a clustered index on the table? If not then you can rebuild
>> til you are blue in the face and it will still stay the same. One of the reasons why most every
>> table should have a clustered index.
> I thought it may be index related (hence my mentioning the indexes). The table itself is 1Gb in
> size. Unfortunately, I can't change the indexing (MSCRM - and not a clustered index in sight!) as
> support will be removed if we do.
> Presumably then the fallback is going to be an INDEXDEFRAG and UPDATE STATISTICS only. Strangely,
> The MS CRM Performance Tuning Whitepaper suggests running the DBREINDEX when they have no
> clustered indexes in the DB.
>
>|||You should contact the vendor and tell them to get their act together and
add a proper clustered index. Why do you say that Indexdefrag is the best
option? As we all have pointed out there is nothing you can do to
defragment the tables without a CI. This is true regardless of using
DBREINDEX or INDEXEDEFRAG. As for the non-Clustered indexes (NCI's) you are
still free to use DBREINDEX on them. If you have the maintenance window to
run this you will mostly find it will be faster and cleaner than
Indexdefrag.
Andrew J. Kelly SQL MVP
"Paul Buxton" <spireite@.hotmail.com> wrote in message
news:eKau2hQGFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Sadly, I can't change the database in anyway or we lose support (system is
> MS CRM 1.2). INDEXDEFRAG/UPDATE STATISTICS is probably the best I can do.
>
> John Bandettini wrote:
>> Paul
>> You really need a clustered index for reindex or rebuild to be effective
>> in removing fragmentation. Create a clustered index and then drop it.
>> This will reduce your fragmentation, and due to not having a clustered
>> index your tables will not fragment as quickly afterwards.
>> Hope this helps
>> John
>> "Paul Buxton" wrote:
>>
>>This is quite weird...
>>Despite what table I pick, running a DBCC DBREINDEX(tablename) does not
>>rebuild anything in the indexes, and fragmentation remains (67%).
>>Am I doing something incredibly stupid here or is there an issue I need
>>to be aware of?
>>Table has non-clustered indexes on it only.
>>Any suggestions? Has anyone seen this behaviour before?
>>Thx