Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have two identical tables in two seperate databases on the same instance of SQL server, defined as follows (note this is third party software so I can't change the table schema):-
EDIT: [RouteParamXml] nvarchar NOT NULL is always an empty string
EDIT: All the UserDef fields are empty strings only 110 rows contain data in SecondaryOrderID, ActionUserId and FIXBrokerOrderID are all populated, I can't see any reason why the space discrepancy is so large

CREATE TABLE blah(
    [AuditEventID] [int] NOT NULL,
    [ActionCode] [tinyint] NOT NULL,
    [ActionDate] [datetime] NOT NULL,
    [ActionUserID] [nvarchar](15) NOT NULL,
    [OrderID] [int] NOT NULL,
    [PlaceID] [int] NOT NULL,
    [FIXMsgType] [int] NOT NULL,
    [FIXOrderStatus] [int] NOT NULL,
    [FIXBrokerOrderID] [nvarchar](60) NOT NULL,
    [FilledQty] [float] NOT NULL,
    [Stamp] [varbinary](8) NOT NULL,
    [MarkForDelete] [smallint] NOT NULL,
    [NewOrderAuditTime] [datetime] NOT NULL,
    [ReplaceOrderAuditTime] [datetime] NOT NULL,
    [SendRecvTime] [datetime] NOT NULL,
    [QueueID] [int] NOT NULL,
    [SecondaryOrderID] [nvarchar](255) NOT NULL,
    [RouteParamXml] [nvarchar](max) NOT NULL,
    [UserDef1] [nvarchar](255) NOT NULL,
    [UserDef2] [nvarchar](255) NOT NULL,
    [UserDef3] [nvarchar](255) NOT NULL,
    [UserDef4] [nvarchar](255) NOT NULL,
    [UserDef5] [nvarchar](255) NOT NULL,
    [UserDef6] [nvarchar](255) NOT NULL,
    [FIXOrderID] [int] NOT NULL,
    [OrigFIXOrderID] [int] NOT NULL,
 CONSTRAINT [blah_PK] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [PlaceID] ASC,
    [AuditEventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

One table has 1.3 illion rows and uses about 22MB (Megabytes) of disk spave The other table has 1 million rows and uses 15GB (Gigabytes) of disk spave

I've been poking around various DBCC options and I can't see anything wrong DBCC CHECKALLOC output (good table)

Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594046316544 (type LOB data). FirstIAM (1:390). Root (1:389). Dpages 0.
Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594046316544 (type LOB data). 241 pages used in 29 dedicated extents.
Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594198687744 (type In-row data). FirstIAM (1:362878). Root (1:549074). Dpages 27571.
Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594198687744 (type In-row data). 27646 pages used in 3457 dedicated extents.
Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594198753280 (type Row-overflow data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 72057594188726272, alloc unit ID 72057594198753280 (type Row-overflow data). 0 pages used in 0 dedicated extents.

DBCC CHECKALLOC output (bad table)

Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594046316544 (type LOB data). FirstIAM (1:2845). Root (1:2844). Dpages 0.
Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594046316544 (type LOB data). 1880724 pages used in 235090 dedicated extents.
Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594197901312 (type In-row data). FirstIAM (1:1155704). Root (1:2024010). Dpages 25147.
Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594197901312 (type In-row data). 25216 pages used in 3153 dedicated extents.
Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594197966848 (type Row-overflow data). FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1, partition ID 72057594188005376, alloc unit ID 72057594197966848 (type Row-overflow data). 0 pages used in 0 dedicated extents.

Any ideas where I should start diiging into why this table is so massive

share|improve this question
add comment

1 Answer

What data's in the tables?

My hunch would be that not all of the fields are actually filled in the small table.

The statistics show that "LOB Data" is the major difference; per MS, this bucket contains:

Pages contain varchar(max), nvarchar(max), varbinary(max), text, ntext, xml, and image data.

In other words, take a long, hard look at the contents of this column:

[RouteParamXml] [nvarchar](max)
share|improve this answer
    
Thanks, I was just following the same thought train. It's empty string in every single row –  David Hayes Aug 2 '11 at 20:41
    
In both databases? –  Shane Madden Aug 2 '11 at 20:51
    
Yes, the data will be roughly the same in both databases. It almost looks like the table has allocated/reserved all the LOB pages but left them empty. The DB backup size is 20GB but zips to 375Mb, the good database backs up to 8GB and zips to 450mb –  David Hayes Aug 2 '11 at 21:17
    
Maybe data in the column at one time - try shrinking the database? –  Shane Madden Aug 2 '11 at 21:23
    
I've tried shrinking, this table is an archive table so it's just copies of rows that used to exist in another database. The data in the table is only added rather than updated –  David Hayes Aug 2 '11 at 21:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.