DBCC DBREINDEX - unexpected results

I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.

First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?

CliveI'm pretty sure it doesn't rebuild secondary indexes when you reindex the clustered index. Keep in mind it's best to reindex the clustered index before reindexing the nonclustered indexes. However, you can use this statement to reindex every index on a table.

DBCC DBREINDEX ([TableName], '', [FillFactor])|||First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?Yes, as long as the clustered index is unique, reindexing the clustered index automagically reindexes the non-clustered indices. The only reason I know that rebuilding the clustered index won't re-index the non-clustered indicies is if the clustered index isn't unique.

-PatP|||Running DBCC DBREINDEX on a clustered index, or on a heap (index id 0) will rebuild all of the indexes on a table. There may be some confusion, however on what constitutes rebuilding. It is not necessary that you will get 100% scan density after you run DBCC DBREINDEX, or rebuild indexes manually. The exception is for very small tables (under 8 pages of data). I have found that the scan densities of such tables are immune to any form of rebuilding the indexes. Is this what you are seeing?|||Cool, I didn't know DBCC DBREINDEX rebuilt other indexes if the clustered index was unique.|||I think theat DBCC DBReindex will rebuild all indexes, whether the clustered index is unique or not. I will have to check, though.|||I have gone and checked (surprising with all the meetings, today), and I have confirmed that the non-clustered indexes are rebuilt when you rebuild a non-unique clustered index.
Before:

DBCC SHOWCONTIG scanning 'pricing' table...
Table: 'pricing' (2089058478); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 2009
- Extents Scanned.......................: 257
- Extent Switches.......................: 1136
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 22.16% [252:1137]
- Logical Scan Fragmentation ..............: 33.40%
- Extent Scan Fragmentation ...............: 64.98%
- Avg. Bytes Free per Page................: 2842.2
- Avg. Page Density (full)................: 64.89%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'pricing' table...
Table: 'pricing' (2089058478); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 727
- Extents Scanned.......................: 98
- Extent Switches.......................: 106
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 85.05% [91:107]
- Logical Scan Fragmentation ..............: 1.24%
- Extent Scan Fragmentation ...............: 98.98%
- Avg. Bytes Free per Page................: 40.1
- Avg. Page Density (full)................: 99.50%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After:

dbcc dbreindex (pricing, ind_pricing_modelnumber)

DBCC SHOWCONTIG scanning 'pricing' table...
Table: 'pricing' (2089058478); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned........................: 1624
- Extents Scanned.......................: 205
- Extent Switches.......................: 204
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.02% [203:205]
- Logical Scan Fragmentation ..............: 10.28%
- Extent Scan Fragmentation ...............: 16.59%
- Avg. Bytes Free per Page................: 1596.7
- Avg. Page Density (full)................: 80.27%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'pricing' table...
Table: 'pricing' (2089058478); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 982
- Extents Scanned.......................: 124
- Extent Switches.......................: 123
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.19% [123:124]
- Logical Scan Fragmentation ..............: 12.42%
- Extent Scan Fragmentation ...............: 52.42%
- Avg. Bytes Free per Page................: 1609.1
- Avg. Page Density (full)................: 80.12%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

EDIT: I forgot to add the sp_helpindex output:

index_name index_description index_keys
--------------------------------------- --------------------------------------------------------------- ----------------------------------------------------------------------------
ind_pricing_modelnumber clustered located on PRIMARY ModelNumber, QuantityLow
PK_pricing nonclustered, unique, primary key located on PRIMARY SalesOrg, DistChan, PriceCondition, ModelNumber, QuantityLow|||The need to rebuild all non-clustered indexes when clustered index gets rebuilt or recreated is NOT governed by the characteristic of the clustered index (unique or not unique) but by the architecture of it. Non-clustered indexes either have to be updated or re-created to point to data pages that have been created by rebuilding/recreating of the clustered index.|||BOL:

Nonclustered indexes have different pointers to data rows depending on whether or not a clustered index is defined for the table. If there is a clustered index the leaf rows of the nonclustered indexes use the clustered index keys to point to the data rows. If the table is a heap, the leaf rows of nonclustered indexes use row pointers. If you drop a clustered index on a table with nonclustered indexes, all the nonclustered indexes are rebuilt to replace the clustered index keys with row pointers.

Similarly, when the clustered index of an indexed view is dropped, all nonclustered indexes on the same view are dropped automatically.

Rebuilding a clustered index by deleting the old index and then re-creating the same index again is expensive because all the secondary indexes use the clustering key to point to the data rows. If you simply delete the clustered index and re-create it, you cause all the nonclustered indexes to be deleted and re-created twice.|||Thank you all for the replies. I ran a test dbcc dbreindex on the table in question noting the dbcc showcontig about before and after (results below). Initially, looking at Scan Density, it appears that nothing has happened to the secondary indexes. This is what I noticed first time around and why posted the question. However, on closer examination, the secondary indexes have been re-indexed because the 'Page Count' has been reduced. Other table in the same database with clustered indexes show much better results for scan density after re-indexing on secondary indexes. A few tables like the one in question behave differently with Scan Density looking bad and staying that way even after re-indexing. So, why is this? Is it a problem? Can something be done about it?

Clive

Before:-

IndexName IndexId PageCount ScanDens Logical Frag
clusterInd 1 123121 18 37
SecInd1 60 15264 15 40
SecInd2 3 12822 16 39
SecInd3 61 18953 22 27
SecInd4 59 14290 15 43
SecInd5 31 17782 15 40

After:-
IndexName IndexId PageCount ScanDens Logical Frag
clusterInd 1 96610 100 0
SecInd1 60 15911 15 41
SecInd2 3 13597 15 41
SecInd3 61 19682 22 28
SecInd4 59 14862 14 44
SecInd5 31 18398 15 41|||By the way, the post in this thread that stated that DBCC DBREINDEX against 'index 0' (a table without a clustered index) made me think a bit... My strategy has been to reindex specific indexes rather than at the table level. In other words, I'm specificing the index in the DBCC DBREINDEX commands. What seemed to be the case was that 'Index 0' related to the table because there is no index name. Therefore, if I'm right, it's not actually possible to specificy Index 0? The point is that it's only possible to either:-

DBCC DBREINDEX ('tbl1', '', 80)

or run DBCC DBREINDEX ('tbl1','secIndex1', 80) and so on for all the secondary indexes individually (or only those secondary indexes in need of re-indexing).

Am I right?

Clive|||That is correct. Indexes 0 and 1 are package deals. You get all of the indexes along with them. In order to run dbreindex on only the clustered index (index 1) or on the heap (index 0), you would have to first drop all of the other indexes (indexes 2 - 254). But this accomplishes pretty much the same thing, and is useful when you want to defragment primary keys that have foreign keys attached to them. It is also useful when you are limited in space to reindex in, since dbreindex makes a copy of the index, then switches them at the end. It is a bit painful on those million row tables.|||So far my assumption has been that secondary indexes are 'rebuilt' when a table's clustered index is rebuilt via DBCC DBREINDEX. However, this was brought into doubt based on the fact that I can see that Scan Density on some secondary indexes isn't always improved on some tables when that table's clustered index is rebuild via DBCC DBREINDEX (specifically on secondary indexes that show very low figures for scan density - eg. 18%). Following on from that, I can see that something is done to the secondary indexes even if Scan Density doesn't imrpove because Page Count does improve a lot. I'm getting closer on this but I can't explain it.

I can now add to this another observation that if I run DBCC DBREINDEX directly against one of these secondary indexes where the scan density figure doesn't improve when Index 1 is re-indexed (ie. DBCC DBREINDEX ('tblname','secondaryIndexName',80)), scan density then shows 100% or close to it. The conclusion must be, therefore, that something different happens when a secondary index is reindexed either directly or indirectly by re-indexing the clustered index.

Can anyone here explain to me what these differences are between direct and indrect re-indexing of secondary indexes?

Clive