Peculiar problem with BCP data load

I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
DonTry creating a clustered index on the table, preferably one that is in order
of the data to some degree.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>