Worse Page Compression after upgrade to SQL 2016
-
We have a table with Page compression which is partitioned daily. Before upgrading from SQL 2014 to SQL 2016 each partition was using +/- 1.6GB in space. After the Upgrade the daily partitions are using double the space. Data volumes are consistent. Has anyone experienced this situation before?
- mphelps - Tuesday, October 17, 2017 6:19 AMWe have a table with Page compression which is partitioned daily. Before upgrading from SQL 2014 to SQL 2016 each partition was using +/- 1.6GB in space. After the Upgrade the daily partitions are using double the space. Data volumes are consistent. Has anyone experienced this situation before?
Can you post the entire ddl for both the table and any indices please?
😎 - Eirikur Eiriksson - Tuesday, October 17, 2017 11:22 AMmphelps - Tuesday, October 17, 2017 6:19 AMWe have a table with Page compression which is partitioned daily. Before upgrading from SQL 2014 to SQL 2016 each partition was using +/- 1.6GB in space. After the Upgrade the daily partitions are using double the space. Data volumes are consistent. Has anyone experienced this situation before?
Can you post the entire ddl for both the table and any indices please?
😎Hi Eirikur, Full ddl as follows
CREATE PARTITION SCHEME [psDaily] AS PARTITION [pfDaily] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GOCREATE PARTITION FUNCTION [pfDaily](datetime) AS RANGE LEFT FOR VALUES (N'2017-09-17T00:00:00.000', N'2017-09-18T00:00:00.000', N'2017-09-19T00:00:00.000', N'2017-09-20T00:00:00.000', N'2017-09-21T00:00:00.000', N'2017-09-22T00:00:00.000', N'2017-09-23T00:00:00.000', N'2017-09-24T00:00:00.000', N'2017-09-25T00:00:00.000', N'2017-09-26T00:00:00.000', N'2017-09-27T00:00:00.000', N'2017-09-28T00:00:00.000', N'2017-09-29T00:00:00.000', N'2017-09-30T00:00:00.000', N'2017-10-01T00:00:00.000', N'2017-10-02T00:00:00.000', N'2017-10-03T00:00:00.000', N'2017-10-04T00:00:00.000', N'2017-10-05T00:00:00.000', N'2017-10-06T00:00:00.000', N'2017-10-07T00:00:00.000', N'2017-10-08T00:00:00.000', N'2017-10-09T00:00:00.000', N'2017-10-10T00:00:00.000', N'2017-10-11T00:00:00.000', N'2017-10-12T00:00:00.000', N'2017-10-13T00:00:00.000', N'2017-10-14T00:00:00.000', N'2017-10-15T00:00:00.000', N'2017-10-16T00:00:00.000', N'2017-10-17T00:00:00.000', N'2017-10-18T00:00:00.000', N'2017-10-19T00:00:00.000', N'2017-10-20T00:00:00.000', N'2017-10-21T00:00:00.000')
GOCREATE TABLE [dbo].[CounterDataFacts](
[counterid] [int] NOT NULL,
[CounterDateTime] [datetime] NOT NULL,
[CounterValue] [real] NOT NULL,
[LoadDateTime] [smalldatetime] NOT NULL,
CONSTRAINT [PK_CounterDataFacts_part] PRIMARY KEY CLUSTERED
(
[CounterDateTime] ASC,
[counterid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [psDaily]([CounterDateTime])
) ON [psDaily]([CounterDateTime])
GOCREATE NONCLUSTERED INDEX [counterid] ON [dbo].[CounterDataFacts]
(
[counterid] ASC,
[CounterValue] ASC,
[CounterDateTime] 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, DATA_COMPRESSION = PAGE) ON [psDaily]([CounterDateTime])
GOALTER TABLE [dbo].[CounterDataFacts] ADD CONSTRAINT [DF_CounterDataFacts_part_LoadDateTimedefault] DEFAULT (getdate()) FOR [LoadDateTime]
GO -
And the output of sp_estimate_data_compression_savings?
😎 - Eirikur Eiriksson - Wednesday, October 18, 2017 5:04 AM
Output from sp_estimate_data_compression for 2 partitions pre upgrade and 2 partitions after upgrade.
- mphelps - Wednesday, October 18, 2017 6:42 AMEirikur Eiriksson - Wednesday, October 18, 2017 5:04 AM
Output from sp_estimate_data_compression for 2 partitions pre upgrade and 2 partitions after upgrade.
My original stats provided was based on the following query
set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT pf.name AS pf_name ,
ps.name AS partition_scheme_name ,
p.partition_number ,
ds.name AS partition_filegroup ,
pf.type_desc AS pf_type_desc ,
pf.fanout AS pf_fanout ,
pf.boundary_value_on_right ,
OBJECT_NAME(si.object_id) AS object_name ,
rv.value AS range_value ,
SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
ELSE 0
END) AS num_rows ,
SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,
SUM(CASE ISNULL(si.index_id, 0)
WHEN 0 THEN 0
ELSE 1
END) AS num_indexes
,p.data_compression_desc
FROM sys.destination_data_spaces AS dds
JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0 THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
where pf.name = 'pfDaily'
GROUP BY ds.name ,
p.partition_number ,
pf.name ,
pf.type_desc ,
pf.fanout ,
pf.boundary_value_on_right ,
ps.name ,
si.object_id ,
rv.value
,p.data_compression_desc
ORDER BY p.partition_number;
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply