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: