DBCC DBREINDEX does not work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DBCC DBREINDEX does not work

Author

Topic

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 10:59:42

DBCC SHOWCONTIG shows following information. But after ran
DBCC DBREINDEX ('CM20100', ''), nothing changed.

What I did wrong? Thanks in advance.

******************************************************
DBCC SHOWCONTIG scanning 'CM20100' table...
Table: 'CM20100' (1957582012); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 491
- Extents Scanned..............................: 159
- Extent Switches..............................: 158
- Avg. Pages per Extent........................: 3.1
- Scan Density [Best Count:Actual Count].......: 38.99% [62:159]
- Extent Scan Fragmentation ...................: 99.37%
- Avg. Bytes Free per Page.....................: 946.0
- Avg. Page Density (full).....................: 88.31%

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 11:07:05

what indexes do you have on that table? You posted the DBCC SHOWCONTIG for the heap table, not of an index. You cannot run DBCC DBREINDEX against the heap table.

Also, the stats in there are not very meaningful since there isn't any order in a heap table.

Put a clustered index on that table and then look at DBCC SHOWCONTIG.

-ec

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 12:14:21

Did you mean DBCC DBREINDEX only work for clustered index? Thanks.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 12:26:39

quote:
Originally posted by jeb0323

Did you mean DBCC DBREINDEX only work for clustered index? Thanks.

no, it only works with tables that have indexes. YOu don't seem to have any indexes on this table, or you only showed us the output for the heap table.

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 12:48:01

Here is another example. Nothing changed after I ran dbcc dbreindex. Thanks for help. I am new to this.

DBCC SHOWCONTIG scanning 'ME27645' table...
Table: 'ME27645' (1228023606); index ID: 255, database ID: 16
LEAF level scan performed.
- Pages Scanned................................: 258
- Extents Scanned..............................: 85
- Extent Switches..............................: 84
- Avg. Pages per Extent........................: 3.0
- Scan Density [Best Count:Actual Count].......: 38.82% [33:85]
- Extent Scan Fragmentation ...................: 95.29%
- Avg. Bytes Free per Page.....................: 533.9
- Avg. Page Density (full).....................: 93.40%

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-21 : 12:58:46

This is the 'special' index ID that stores text values - you can't remove fragmentation from it.

What are you trying to achieve with DBREINDEX?

I suggest you read the whitepaper on fragmentation below

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

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 13:43:21

We have a server, its e drive gets fragmented a lot. We have to degrag it every day, see the following:
*************************************************
---> Running: "defrag e:"
Windows Disk Defragmenter
Copyright (c) 2003 Microsoft Corp. and Executive Software International, Inc.

Analysis Report
68.35 GB Total, 13.79 GB (20%) Free, 29% Fragmented (58% file fragmentation)

Defragmentation Report
68.35 GB Total, 17.36 GB (25%) Free, 24% Fragmented (49% file fragmentation)
*********************************************************************************

This server only has one application, and the sql sever data files are located on e drive. So I ran dbcc showcontig to see if there is a problem. Here is one with table view:

**********************************************
ObjectName OjbectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragementation ExtentFragementtation

UPR30300 1767481871 AK6UPR30300 9 0 1815 218331 36 36 36 0 238 1705 3524.88208 56.45067978 13.3059789 227 1706 58.12672043 69.74790192

*************************************************************

From books online, I see dbreindex can fix the problem. I ran the dbreindex but nothing changed.

Thanks.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 13:48:09

dbreindex and indexdefrag work only on indexes. From the looks of your DBCC SHOWCONTIG output, you do not have any indexes.

btw, showcontig is only going to show fragmentation on the inside of the datafiles or internal fragmentation. The OS level defrag utility is showing you external file fragmentation. These are two different things.

Running dbreindex and/or indexdefrag won't do anything to fix the external file fragmentation you are concerned with.

-ec

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 14:02:08

The last example is for index, isn't it? It was just in table view:

*************************************************
DBCC SHOWCONTIG scanning 'UPR30300' table...
Table: 'UPR30300' (1767481871); index ID: 9, database ID: 9
LEAF level scan performed.
- Pages Scanned................................: 1815
- Extents Scanned..............................: 238
- Extent Switches..............................: 1705
- Avg. Pages per Extent........................: 7.2
- Scan Density [Best Count:Actual Count].......: 13.31% [227:1706]
- Logical Scan Fragmentation ..................: 58.13%
- Extent Scan Fragmentation ...................: 69.75%
- Avg. Bytes Free per Page.....................: 3524.9
- Avg. Page Density (full).....................: 56.45%
*********************************************************************

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 14:16:50

here are some articles to read on this issue

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

read this along with the article paul linked and that should give you a good understanding of what to look for.

-ec

jeb0323
Starting Member

26 Posts

Posted - 2006-02-21 : 14:33:13

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources