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
