help with showcontig

Hi,

i'm on windows nt4 sp6a , sql server 7.00.0842

i have something that i don't understand.
The table 'source' is taking lot of space but when i bcp out, the file take
only 11MB (fixed delimiter option)
i issue showontig and it says me that Avg. Page Density
(full)................: 4.33%
so if i understand there are a lot of space wasted per page
so i issue DBCC DBREINDEX('dbo.source', '', 50) and i expect to have Avg.
Page Density (full)................: 50%
but it's still the same even after dbcc update usage on that table

at alst, i've done a select * into test from source and the test table take
only 11MB like the bcp out. (1290 pages insted of 32923 pages of the source
table)

so what can i do to reduce the 'source' table ?

if anayone can help me ?

the table 'source' the ddl is :

CREATE TABLE dbo.source
(
f1_id numeric(18,0) NOT NULL,
f2_id numeric(18,0) NOT NULL,
f3_id numeric(28,0) IDENTITY,
f4_id char(300) NOT NULL,
CONSTRAINT pk_source_compid_tranid
PRIMARY KEY NONCLUSTERED (f1_id,f2_id,f3x_id) WITH FILLFACTOR=50 ON
[PRIMARY]
)

sp_spaceused 'source'

name rows reserved data index_size unused
source 29701 287088 KB 263400 KB 23400 KB 288 KB

USE appli
go
DBCC UPDATEUSAGE('appli', 'dbo.source')
WITH COUNT_ROWS
go
DBCC SHOWCONTIG(702625546)
go
DBCC DBREINDEX('dbo.source', '', 50)
go
DBCC UPDATEUSAGE('appli', 'dbo.source')
WITH COUNT_ROWS
go
DBCC SHOWCONTIG(702625546)

The command executed successfully with no results returned.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'source' table...
Table: 'source' (702625546); index ID: 0, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 32923
- Extents Scanned.......................: 4122
- Extent Switches.......................: 4121
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.85% [4116:4122]
- Extent Scan Fragmentation ...............: 95.41%
- Avg. Bytes Free per Page................: 7743.9
- Avg. Page Density (full)................: 4.33%

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

Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
Index (ID = 4) is being rebuilt.
Index (ID = 5) is being rebuilt.
Index (ID = 6) is being rebuilt.

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

DBCC UPDATEUSAGE: sysindexes row updated for table 'source' (index ID 2):
USED pages: Changed from (2391) to (2390) pages.
RSVD pages: Changed from (2400) to (2401) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'source' (index ID 3):
USED pages: Changed from (201) to (200) pages.
RSVD pages: Changed from (208) to (209) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'source' (index ID 4):
USED pages: Changed from (335) to (334) pages.
RSVD pages: Changed from (344) to (345) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'source' (index ID 0):
USED pages: Changed from (35843) to (35849) pages.
RSVD pages: Changed from (35882) to (35885) pages.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'source' table...

Table: 'source' (702625546); index ID: 0, database ID: 12
TABLE level scan performed.
- Pages Scanned........................: 32923
- Extents Scanned.......................: 4122
- Extent Switches.......................: 4121
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.85% [4116:4122]
- Extent Scan Fragmentation ...............: 95.41%
- Avg. Bytes Free per Page................: 7743.9
- Avg. Page Density (full)................: 4.33%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Jean (test@.est.com) writes:
> so if i understand there are a lot of space wasted per page
> so i issue DBCC DBREINDEX('dbo.source', '', 50) and i expect to have Avg.
> Page Density (full)................: 50%
> but it's still the same even after dbcc update usage on that table
>...
> DBCC SHOWCONTIG scanning 'source' table...
> Table: 'source' (702625546); index ID: 0, database ID: 12
> TABLE level scan performed.

Here is the crux of the biscuit: there is no clustered index on the table.
And since there is no clustered index, DBREINDEX will not touch the
data pages.

You can create a temporary clustered index and then drop that index. That
should leave the data pages unfragmented. However, unless you have very
good reason not to have a clustered index, my recommendation is that you
define one of you existing indexes as clustered, and stick with that,

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> Here is the crux of the biscuit: there is no clustered index on the
> table. And since there is no clustered index, DBREINDEX will not
> touch the
> data pages.

thx it works !!
good day