0

On SQL Server 2016 (13.0.6300.2) we have one table that have two XML columns and around 1 500 000 rows. Size of table is around 150 GB. What is the best option to compress this table ? I was checking PAGE compression on dev environment but it saves only 3%. Is it chance that on Sql Server 2019 (planned update in near future) will be better ?

1 Answer 1

3

XML data isn't compressed using page compression if the data is off-row. By default, the XML data (all types of LOB data) is in-row if the row is smaller than 8 000 bytes.

Unless I mis-calculate, you average 107 000 bytes per row. I.e., it is very likely that the vast amount of your XML data is off-row. Meaning page compression won't help (much).

SQL Server 2022 has XML compression. See this and this (the XML_COMPRESSION option).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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