Understanding the differences in the storage space used by two tables

Question

I have two tables in which, more or less, one contains a subset of data of the other one. There is some data I delete from the first table, which is inserted in the second one as historical data.

These are the defintions:

CREATE TABLE [dbo].[CIC_DESLUNGHE](
    [CD_CIC_DESLUNGHE] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE] PRIMARY KEY CLUSTERED 
(
    [CD_CIC_DESLUNGHE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [dbo].[CIC_DESLUNGHE_STORICI](
    [CD_CIC_DESLUNGHE_STORICI] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE_STORICI] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI_STORICI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE_STORICI_1] PRIMARY KEY CLUSTERED 
(
    [CD_CIC_DESLUNGHE_STORICI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The second table contains a subset of data that I need it as historicization of my data.

Table                   Rows        TotalSpace MB
---------------------   ----------  -------------
CIC_DESLUNGHE_STORICI   12.709.497  48214,75
CIC_DESLUNGHE           24.827.337  5675,49

What leaves me astonished is that the total storage space utilized. The subset table use 10 times the space of the other.

Any idea why this is happening?

I tried rebuilding the clustered index of CIC_DESLUNGHE_STORICI, but the storage only dropped from 48 GB to 47 GB.

-- EDIT --

This is the result I got with the query of Josh Darnell

Answer 1

Based on the information given, one explanation is that the size of the data being stored in the nvarchar(max) column used to be much larger than it is now.

• ~12 million historical rows averaging 3.88 KB per row
• ~24 million live rows averaging 0.23 KB per row

You can double check this by using the dynamic management function sys.dm_db_index_physical_stats on each of the two tables, specifically looking at the "avg_record_size_in_bytes" column. Something like this:

SELECT 
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats 
(
    DB_ID(N'YourDatabaseName'), 
    OBJECT_ID(N'CIC_DESLUNGHE'), 
    NULL, 
    NULL , 
    'DETAILED'
) ips;

And

SELECT 
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats 
(
    DB_ID(N'YourDatabaseName'), 
    OBJECT_ID(N'CIC_DESLUNGHE_STORICI'), 
    NULL, 
    NULL , 
    'DETAILED'
) ips;

Compare the results of the two queries and see if there are any surprising discrepancies.

I left other filters off the function call because this will also show nonclustered indexes. You didn't mention how you arrived at the numbers in your question, but if they include NC indexes on the tables, perhaps those are contributing to the size difference.