1

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';

TABLE_ESTIMATE

Estimate Row Compression:

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

enter image description here

Index Sizes:

enter image description here

Current Compression Settings:

enter image description here

----- UPDATE: After Degragmenting

enter image description here

New Compression Estimates:

enter image description here

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';

enter image description here

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)

enter image description here

Now, the estimate for 'NONE:

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

enter image description here

Now, the estimate for 'ROW:

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

enter image description here

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;

enter image description here

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]
2

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.

  • @ ScottHodgin (atScottHodgin isn't working) I was kind of suprised to see any difference, but again it is an 'estimate'. So, after reading the article, the clustered index is within a fairly small variance, it's just the other indexes that are significantly different. I will run a defrag and see what that does though. – John Aug 29 '16 at 14:56
  • Let us know if the rebuild/reorg makes any difference in the numbers. – Scott Hodgin Aug 29 '16 at 16:48
  • I've done that and the estimated sizes for the non-clustered indexes are still greatly smaller than they would be if I applied the compression type that I'm trying to get the estimate for. I've updated my post with more information. – John Aug 29 '16 at 17:12
  • can you post the table DDL for FOO - the index definitions might help also – Scott Hodgin Aug 29 '16 at 17:13
  • Well - it 'seems' that the link I provided in the answer shows where another individual was seeing this same weird behavior and they even opened a Microsoft Connect Item - Microsoft closed that Connect Item stating "Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server." - Meaning, I guess, "hey, that's interesting, but we're not going to fix it" - I agree that it's strange, but I'm out of ideas :( – Scott Hodgin Aug 29 '16 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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