DBCC DBREINDEX question

Question

DBCC DBREINDEX question

barsuk

SSChampion

Points: 12280
More actions
May 9, 2007 at 8:05 pm

#173589

Hi All,
Encountered that issue for the first time (SQL Server 2000). On my previous position as a DBA, I DBREINDEX all indexes with scan density less 80% on a regular basis, despite the row count. And I keep track of scan density before and after DBCC DBREINDEX. (I read that for tables with row count less or about 1000 we could neglect scan density). All tables ( despite the row count) have been reindexed without any problems.
In the new environment when I am trying to reindex all tables, only big tables are being reindexed, when tables with row count less or about 1000 are having the same scan density (with DBCC SHOWCONTIG) before and after DBREINDEX.
Any ideas on how to fix that issue?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Are there clustered indexes on these small tables? Can you post the SHOWCONTIG results for one of the tables in question? According to Microsoft, you should not be concerned with index fragmentation on tables made up of 1000 pages or less. Is there a reason why you are concerned with these small tables?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

barsuk SSChampion Points: 12280 More actions · Answer 2

They are Cl index on small tables:

DBCC SHOWCONTIG scanning 'tblTag' table...

Table: 'tblTag' (1110295010); index ID: 1, database ID: 12

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 50.00%

- Avg. Bytes Free per Page.....................: 276.0

- Avg. Page Density (full).....................: 96.59%

DBCC execution completed. If DBCC printed error messages, contact your system administrator

I know about 1000 rows rule, but thought if someone was able to fix it.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Without having the DDL for the table and indexes I can only offer the step for a general fix. Unfortunately it requires a little bit of down time.

script out the indexes
script out any triggers
bcp out the data
drop the indexes
bcp in the data
add the indexes
add the triggers
update statistics
execute sp_recompile on the table
execute sp_refreshview on any views that use the table

Based on the fact that the table is only 6 extents all of the above steps should be able to be accomplished in 5 minutes or less I would guess. However there could be just a few steps missing if the table is involved in any foreign key relationships - basically a bunch of alters which should not adversly affect the overall execution time of the process. Once the process is complete te table should be in great shape. You may also want to look at table volitilty too. If the table is prone to changes you may also want to adjust fill factors on the indexes.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."