Sql-server – SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

UPDATE – I originally said 'estimate significantly lower when set to 'NONE'. I'm amending that to say 'estimate significantly lower when set to [the compression in use].

When I execute the sp_estimate_data_compression_savings procedure on various tables with the 'data_compression' parameter set to 'NONE', it shows large size differentials on the non-clustered indexes.

The 'size_with_current_compression_setting' is correct and reflects the actual size of the indexes. The 'size_with_requested_compression_setting' though is much smaller.

If the request is for table/index size with compression set to its current compression type, why is it showing that the indexes would be far smaller than they actually would be if I chose that compression type?

Here's an example of what I'm seeing with real values, names changed:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Estimate Row Compression:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Index Sizes:

Current Compression Settings:

—– UPDATE: After Degragmenting

New Compression Estimates:

Just for kicks, I compressed the indexes with 'NONE':

ALTER INDEX [INDEX_2] ON dbo.FOO_TABLE REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = NONE);

Compression estimates again:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Finally, I set the compression level for all 4 indexes to 'ROW' (e.g.):

ALTER TABLE dbo.FOO_TABLE REBUILD PARTITION = ALL  
WITH (DATA_COMPRESSION = ROW);   

ALTER INDEX INDEX_2 ON dbo.FOO_TABLE REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = ROW); // (And INDEX_3 & INDEX_4)

Now, the estimate for 'NONE:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'NONE';

Now, the estimate for 'ROW:

EXEC sp_estimate_data_compression_savings 'dbo', 'FOO_TABLE', NULL, NULL, 'ROW';

Checking fragmentation again:

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'[DATABASE]'), 
OBJECT_ID(N'dbo.FOO_TABLE'), NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Here's the DDL for the table:

CREATE TABLE [dbo].[FOO_TABLE](
    [COL1] [char](6) NOT NULL,
    [COL2] [char](4) NOT NULL,
    [COL3] [datetime] NOT NULL,
    [COL4] [datetime] NOT NULL,
    [COL5] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [COL6] [int] NULL,
    [COL7] [int] NULL,
    [COL8] [char](4) NOT NULL,
    [COL9] [datetime] NULL,
    [COL10] [char](5) NULL,
    [COL11] [char](1) NOT NULL,
    [COL12] [char](1) NOT NULL,
    [COL13] [datetime] NULL,
    [COL14] [char](1) NOT NULL,
    [COL15] [char](1) NOT NULL,
    [COL16] [int] NULL,
    [COL17] [char](1) NOT NULL,
    [COL18] [datetime] NULL,
    [COL19] [char](8) NOT NULL,
    [COL20] [datetime] NOT NULL,
    [COL21] [timestamp] NULL,
 CONSTRAINT [pk_meterset_volume] PRIMARY KEY CLUSTERED 
(
    [COL1] ASC,
    [COL2] ASC,
    [COL3] ASC,
    [COL4] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_2] ON [dbo].[FOO_TABLE]
(
    [COL16] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [INDEX_3] ON [dbo].[FOO_TABLE]
(
    [COL3] ASC,
    [COL1] ASC,
    [COL9] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_4] ON [dbo].[FOO_TABLE]
(
    [COL5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]