January 27, 2004 at 5:53 pm
Well let's try this again. Appear to have lost last post attempt.
Although DTS is being used I do not believe this is a DTS problem per se.
I have two tables that loaded exactly the same data, have "slightly" different format but use significantly different reserved space. I used Table size script by ashokjanjani Posted: 06/07/2003 to verify why new database was so much larger and found the table size usage difference on this and other tables.
Table XXXWorkCash is the old table. It was created by previous analyst via DTS import create and does not have indexes. Data was loaded via straight DTS Copy Column.
Table BKPS_XXX_zWrk_Cash is new table created via DDL with indexes. Data was loaded via DTS ActiveX Script for data interrogation. New table is on same server as old table but in different database.
The old table took 1992 KB for 12690 records using 1960 KB. The new table took 823712 KB for the same 12690 records while using 101528 KB. Neither table has fields containing NULLS. I checked new table via LEN() that columns were not being padded with spaces. I do not believe the format differences (NVarChar vs VarChar, float vs money and addition of Identity field) can explain the large difference in space utilization. Removal of indexes had very little effect.
I have included the results of ashokjanjani’s table size script for each table plus Query Analyzer – Script Object as Create for each table.
Anyone out there have any ideas on why this is occurring? Did I miss something on create or load? The ‘Real’ DDL for new table is available if needed.
Since we are tight on disk space this problem is a killer for new methodology.
##########################################################################
RecCnt Reserved Data(used) Index UnUsed
XXXWorkCash 12690 1992 KB 1960 KB 8 KB 24 KB
BKPS_XXX_zWrk_Cash 12690 823712 KB 101528 KB 1648 KB 720536 KB
######################################################################
CREATE TABLE dbo.XXXWorkCash (
LeaseNum nvarchar (255) NULL ,
DueDate nvarchar (255) NULL ,
InvdtlAmount float NULL ,
InvdtlTranCode nvarchar (255) NULL ,
InvdtlInvoiceDesc nvarchar (255) NULL
) ON PRIMARY
GO
CREATE TABLE dbo.BKPS_XXX_zWrk_Cash (
RecordSeqId int IDENTITY (1, 1) NOT NULL ,
LeaseNum varchar (255) NULL ,
DueDate datetime NULL ,
InvdtlAmount money NULL ,
InvdtlTranCode varchar (255) NULL ,
InvdtlInvoiceDesc varchar (255) NULL ,
Cln_BypassRec varchar (1) NULL CONSTRAINT DF__BKPS_Toky__Cln_B__1BF30A66 DEFAULT ('N'),
CONSTRAINT pk_BKPS_TokyoLCA049_zWrk_Cash PRIMARY KEY NONCLUSTERED
(
RecordSeqId
) WITH FILLFACTOR = 100 ON PRIMARY
) ON PRIMARY
GO
SmithDM
January 28, 2004 at 3:31 am
What does DBCC SHOWCONTIG return for each table?
Cheers,
- Mark
January 28, 2004 at 9:38 am
Mccork
Thanks for come back - the DBCC SHOWContig you requested for the files are below.
As expected the problem is in the data portion not indexes. Unfortunately there is no FillFactor for data only indexes. Since NVarChar uses 2 bytes per character, the switch to VarChar should have reduced the space requirements. The final VarChar really should be Char but that should not have major impact. Float (8 bytes) vs Money (8 bytes) and addition of Integer Identity (4 bytes) also should not have had major impact.
Neither database has ANSI NULLS Default option checked. ANSI_NULLS were set on at time procedure creating new tables was created. Insert was via VB ActiveX Script so I suspect ANSI_NULLS were also set on. Both databases (on same server) are using the same collation code page (COLLATE SQL_Latin1_General_CP1_CI_AS). Based on note in BOL this is almost acting like 2 byte Collation code page causing full space retention for defined VarChar (255) fields but why on one database only?
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
DBCC SHOWContig (BKPS_XXX_zWrk_Cash) with all_Indexes
DBCC SHOWCONTIG scanning 'BKPS_XXX_zWrk_Cash' table...
Table: 'BKPS_XXX_zWrk_Cash' (452912685); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 12690
- Extents Scanned..............................: 12690
- Extent Switches..............................: 12689
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 12.51% [1587:12690]
- Extent Scan Fragmentation ...................: 3.06%
- Avg. Bytes Free per Page.....................: 8027.2
- Avg. Page Density (full).....................: 0.83%
DBCC SHOWCONTIG scanning 'BKPS_XXX_zWrk_Cash' table...
Table: 'BKPS_XXX_zWrk_Cash' (452912685); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 21
- Extent Switches..............................: 20
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 19.05% [4:21]
- Logical Scan Fragmentation ..................: 12.00%
- Extent Scan Fragmentation ...................: 95.24%
- Avg. Bytes Free per Page.....................: 481.9
- Avg. Page Density (full).....................: 94.05%
DBCC SHOWCONTIG scanning 'BKPS_XXX_zWrk_Cash' table...
Table: 'BKPS_XXX_zWrk_Cash' (452912685); index ID: 3, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 128
- Extents Scanned..............................: 123
- Extent Switches..............................: 125
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 12.70% [16:126]
- Logical Scan Fragmentation ..................: 54.69%
- Extent Scan Fragmentation ...................: 97.56%
- Avg. Bytes Free per Page.....................: 3618.7
- Avg. Page Density (full).....................: 55.29%
DBCC SHOWCONTIG scanning 'BKPS_XXX_zWrk_Cash' table...
Table: 'BKPS_XXX_zWrk_Cash' (452912685); index ID: 4, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 46
- Extents Scanned..............................: 42
- Extent Switches..............................: 41
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 14.29% [6:42]
- Logical Scan Fragmentation ..................: 39.13%
- Extent Scan Fragmentation ...................: 95.24%
- Avg. Bytes Free per Page.....................: 193.5
- Avg. Page Density (full).....................: 97.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
DBCC SHOWContig (XXXWorkCash) with all_Indexes
DBCC SHOWCONTIG scanning 'LCAWorkCash' table...
Table: 'XXXWorkCash' (629577281); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 245
- Extents Scanned..............................: 32
- Extent Switches..............................: 31
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 96.88% [31:32]
- Extent Scan Fragmentation ...................: 3.13%
- Avg. Bytes Free per Page.....................: 380.1
- Avg. Page Density (full).....................: 95.30%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SmithDM
January 28, 2004 at 11:13 am
To all out there
I have semi found the problem but the solution is not workable.
In original configuration I was committing on each record within the DTS transform. Though much slower than single commit, I needed to do this based on bad data from external source (no control of quality). I needed to accept all good data and be able to identify specific record number in error from input text file. Apparently the single commit causes only one record to be inserted per page. When commit is set to 0 (complete batch) multiple records per page occurs. This of course saves a great deal of space.
This is the first time I was aware that the frequency of commits had any bearing on data storage. Since many OLAP systems commit for each transaction why does not this happen to them? Is this a unique problem to DTS transforms.
SmithDM
January 28, 2004 at 3:55 pm
I've seen T-SQL code that commits every record. Although it's slow I have never seen this 1-row-per-page situation occur.
I think it is unique to DTS transforms, or at least to the underlying processes of DTS.
Cheers,
- Mark
January 28, 2004 at 4:36 pm
mccork - you are back. thanks again. I have reposted this Commit=1 vs Commit=0 stream over in DTS where it may be more appropriate.
I, like you have not seen this space utilization problem before. Very strange. DBCC definitely shows or appears to show one per page when commit=1.
SmithDM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply