SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

Question

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]

Answer 1

Check out this link - http://www.bobpusateri.com/archive/2012/04/compression-estimates-that-dont-make-sense/

BOL states that “If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.”

The above link does point to a Microsoft Connect item that was opened that seemed 'somewhat' similar, but not exactly like your issue. Microsoft close it indicating no fix would be forthcoming.