Lesson Learned #159: Compressing data and LOB data type in Azure SQL Managed Instance

Published 02-02-2021 10:52 AM 929 Views

Today, I worked on a service request that our customer asked to compress data because they are reaching the limit of the database size and they don't want to scale up to the next database tier to have more available space. 

 

Besides the multiple things that we have to reduce the size and as our customer needs to have a quick solution and the CPU usage is not too much, we suggested to use compression. 

 

In this situation, we have a table that has a XML column that is using the almost space of the database and we put focus to try to compress the data. 

 

In my first proof of concept before sending this recomendation, I saw a thing that we need to know before compressing the data when you have a LOB data types. 

 

SQL Server will not compress data when the size of the data takes more than the maximum size of data page (8096 bytes), so, for this reason, we need to analyze if this solution applies. Let me show you an example: 

 

We have two tables Compressed and NotCompressed with this layout:

 

create table Compressed
(ID INT IDENTITY(1,1), DETAILS XML )

create table NotCompressed
(ID INT IDENTITY(1,1), DETAILS XML )

ALTER TABLE Compressed REBUILD PARTITION = ALL  
WITH (DATA_COMPRESSION = PAGE);   
GO
ALTER TABLE NotCompressed REBUILD PARTITION = ALL  
WITH (DATA_COMPRESSION = PAGE);   

INSERT INTO Compressed (details) values(replicate('x',900))
INSERT INTO Compressed (details) values(replicate('x',9000))

INSERT INTO NotCompressed (details) values(replicate('x',9000))
INSERT INTO NotCompressed (details) values(replicate('x',90000))

-- Run multiple times the following queries --
INSERT INTO Compressed (details) Select details FROM Compressed
INSERT INTO NotCompressed (details) Select details FROM NotCompressed

 

After running multiple times the INSERT INTO...SELECT we could see the rows with more than 8096 bytes are not compressed. My suggestion is to use the sp_estimate_data_compression_savings for knowing the savings that you are going to have. 

 

In this situation, it is important, to know how much data that you have in your table. Also, if you are using replication, for example, from OnPremise-Azure or Azure-OnPremise please review the following details 

 

Enjoy!!!

%3CLINGO-SUB%20id%3D%22lingo-sub-2111611%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23159%3A%20Compressing%20data%20and%20LOB%20data%20type%20in%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111611%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20asked%20to%20compress%20data%20because%20they%20are%20reaching%20the%20limit%20of%20the%20database%20size%20and%20they%20don't%20want%20to%20scale%20up%20to%20the%20next%20database%20tier%20to%20have%20more%20available%20space.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBesides%20the%20multiple%20things%20that%20we%20have%20to%20reduce%20the%20size%20and%20as%20our%20customer%20needs%20to%20have%20a%20quick%20solution%20and%20the%20CPU%20usage%20is%20not%20too%20much%2C%20we%20suggested%20to%20use%20compression.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20we%20have%20a%20table%20that%20has%20a%20XML%20column%20that%20is%20using%20the%20almost%20space%20of%20the%20database%20and%20we%20put%20focus%20to%20try%20to%20compress%20the%20data.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20my%20first%20proof%20of%20concept%20before%20sending%20this%20recomendation%2C%20I%20saw%20a%20thing%20that%20we%20need%20to%20know%20before%20compressing%20the%20data%20when%20you%20have%20a%20LOB%20data%20types.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20will%20not%20compress%20data%20when%20the%20size%20of%20the%20data%20takes%20more%20than%20the%20maximum%20size%20of%20data%20page%20(8096%20bytes)%2C%20so%2C%20for%20this%20reason%2C%20we%20need%20to%20analyze%20if%20this%20solution%20applies.%20Let%20me%20show%20you%20an%20example%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20have%20two%20tables%20Compressed%20and%20NotCompressed%20with%20this%20layout%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Ecreate%20table%20Compressed%0A(ID%20INT%20IDENTITY(1%2C1)%2C%20DETAILS%20XML%20)%0A%0Acreate%20table%20NotCompressed%0A(ID%20INT%20IDENTITY(1%2C1)%2C%20DETAILS%20XML%20)%0A%0AALTER%20TABLE%20Compressed%20REBUILD%20PARTITION%20%3D%20ALL%20%20%0AWITH%20(DATA_COMPRESSION%20%3D%20PAGE)%3B%20%20%20%0AGO%0AALTER%20TABLE%20NotCompressed%20REBUILD%20PARTITION%20%3D%20ALL%20%20%0AWITH%20(DATA_COMPRESSION%20%3D%20PAGE)%3B%20%20%20%0A%0AINSERT%20INTO%20Compressed%20(details)%20values(replicate('x'%2C900))%0AINSERT%20INTO%20Compressed%20(details)%20values(replicate('x'%2C9000))%0A%0AINSERT%20INTO%20NotCompressed%20(details)%20values(replicate('x'%2C9000))%0AINSERT%20INTO%20NotCompressed%20(details)%20values(replicate('x'%2C90000))%0A%0A--%20Run%20multiple%20times%20the%20following%20queries%20--%0AINSERT%20INTO%20Compressed%20(details)%20Select%20details%20FROM%20Compressed%0AINSERT%20INTO%20NotCompressed%20(details)%20Select%20details%20FROM%20NotCompressed%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20running%20multiple%20times%20the%20INSERT%20INTO...SELECT%20we%20could%20see%20the%20rows%20with%20more%20than%208096%20bytes%20are%20not%20compressed.%20My%20suggestion%20is%20to%20use%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-stored-procedures%2Fsp-estimate-data-compression-savings-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Esp_estimate_data_compression_savings%3C%2FA%3E%20for%20knowing%20the%20savings%20that%20you%20are%20going%20to%20have.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20situation%2C%20it%20is%20important%2C%20to%20know%20how%20much%20data%20that%20you%20have%20in%20your%20table.%20Also%2C%20if%20you%20are%20using%20replication%2C%20for%20example%2C%20from%20OnPremise-Azure%20or%20Azure-OnPremise%20please%20review%20the%20following%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fdata-compression%2Fdata-compression%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Edetails%26nbsp%3B%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2111611%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20asked%20to%20compress%20data%20because%20they%20are%20reaching%20the%20limit%20of%20the%20database%20size%20and%20they%20don't%20want%20to%20scale%20up%20to%20the%20next%20database%20tier%20to%20have%20more%20available%20space.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Feb 02 2021 10:52 AM
Updated by: