Sql Server 2019, page compression, pre-/post-application
0
votes
1
answer
68
views
I've got this ETL process that produces json blobs from a corpus once a week. I've been going through the process trying to optimize it.
I ran sp_estimate_data_compression_savings on the output table, and it said it was highly compressible, so I ran it. The savings was a 152 gig table down to a 22 gig table. I thought Great! I'll just declare the table with page compression when we load into it.
Now, I know that the compression isn't always as efficient when you load into an empty table as it is applying the compression afterwards, but to my surprise, when I loaded into the empty table with page compression declared, I got effectively ZERO compression. The index (which is not very compressible) got the expected compression but the heap got none. sp_estimate_data_compression_savings still says it can get great compression *reapplying* PAGE compression.
I read some documentation that said loading into an empty table with PAGE compression starts trying to do ROW and then compresses PAGE when it hits the end of a block and thinks it can do more compression. I took that as explaining why you don't always get as much compression as applying it post-load, but ZERO?
And additional post step takes a while, and I've often been willing to sacrifice 10-20% of the compression to spread the cost. I'm just surprised that pre- application got zero compression on load. Is there something I'm missing here?
CREATE TABLE [dbo].[JSONmaster2](
[ID] [int] NOT NULL,
[JSON] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[InsertDate] [smalldatetime] NOT NULL
)
WITH(DATA_COMPRESSION = PAGE)
GO
CREATE UNIQUE NONCLUSTERED INDEX [ixJSONmaster-KeyID] ON [dbo].[JSONmaster2]
([ID] ASC)
WITH (DATA_COMPRESSION = PAGE)
Asked by user1664043
(379 rep)
Jul 31, 2025, 11:46 PM
Last activity: Aug 4, 2025, 10:27 AM
Last activity: Aug 4, 2025, 10:27 AM