The largest value in a row can be the document tucked inside it. XML compression in SQL Server 2022 reduces stored XML without changing the document you query. The useful question is how much space it saves on your workload.
Start With a Separate XML Sample
XML keeps structure and values together. That convenience brings storage costs, especially when documents contain repeated element names. SQL Server already stores XML in an internal format.
Compression adds another storage choice for off-row XML values. It doesn't turn your document into a different data type.
I check the XML columns before proposing a table rebuild. Large ordinary string columns need a different discussion. This feature arrived in SQL Server 2022.
Use that version or later for the examples below. Run them in a disposable database, with permission to create and alter tables.
The table uses a clustered primary key and an XML payload. The generated documents are sample input, not evidence of savings. Real documents differ in structure and size.
Keep a representative copy of your own data for the storage comparison. A tiny sample proves the commands, not the business case.
CREATE TABLE dbo.XmlCompressionDemo
(
DocumentId int NOT NULL PRIMARY KEY,
Payload xml NOT NULL
);
INSERT dbo.XmlCompressionDemo(DocumentId, Payload)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id),
CONVERT(xml, N'<document><item><name>Sample</name><detail>'
+ REPLICATE(N'repeated document content ', 100)
+ N'</detail></item></document>')
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
EXEC sys.sp_spaceused N'dbo.XmlCompressionDemo', @updateusage = N'TRUE';Record Space Before XML Compression
Save the result from sp_spaceused before changing the table. The reserved column covers allocated space. The data and index_size columns divide that allocation into useful categories.
Unused space belongs in the comparison too. Compare the same object before and after, with the same rows present.
Don't use the length of an XML string as the disk measurement. A conversion to nvarchar reports a text representation. It doesn't report the allocation used by the internal XML storage.
Likewise, a smaller payload returned to the application doesn't establish smaller database files. These are separate measurements with separate meanings.
Take the baseline when the table is quiet. New rows arriving during the rebuild confuse the comparison. Record the indexes, partitioning, and ordinary compression settings too.
If you change several things together, you lose the reason for the difference. A storage test needs a stable starting point, not a moving target.
Rebuild the Table With XML Compression
Enable the option with a table rebuild. This rewrites storage and consumes resources. Plan for logging, working space, and locks.
Don't paste a maintenance command into a busy production session because the syntax looks short. The engine still has to do the physical work behind those few words.
XML compression is separate from ROW and PAGE compression. You can evaluate those choices independently. Don't assume an existing PAGE setting already compresses off-row XML.
The option below targets that XML storage specifically. Existing application queries continue to read an xml value, with the same methods and document content.
I keep the first experiment limited to one representative table. That makes the space and CPU comparisons easier to explain. A database-wide change creates a much harder rollback discussion.
If this rebuild is unsuitable for the available window, schedule the test elsewhere. The documentation doesn't reserve a maintenance window for you.
ALTER TABLE dbo.XmlCompressionDemo
REBUILD WITH (XML_COMPRESSION = ON);
EXEC sys.sp_spaceused N'dbo.XmlCompressionDemo', @updateusage = N'TRUE';
Inspect Every Partition and Index
The table setting is visible in sys.partitions. Read xml_compression_desc alongside index_id and partition_number. One table name doesn't guarantee one storage setting across all its partitions.
A partitioned object deserves a partition-by-partition check. Keep the index name in the output so the result has an owner you can identify.
XML indexes also support this feature, but their setting needs explicit attention. Compressing the base table doesn't automatically establish the desired option on every XML index. Primary and secondary XML indexes store their own structures. Inspect them separately and plan their rebuilds as part of the same storage review.
The following query lists the partition settings without modifying them. An OFF result is a configuration fact, not a fault. Some workloads deserve that choice.
Look at the data compression column too, so nobody confuses the two options. The engine is precise about the distinction. Our maintenance notes should be equally precise.
SELECT i.name AS IndexName, p.index_id, p.partition_number,
p.data_compression_desc, p.xml_compression_desc
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.object_id = OBJECT_ID(N'dbo.XmlCompressionDemo')
ORDER BY p.index_id, p.partition_number;Include the Queries That Read Documents
Compression trades storage work against CPU work. Test the XML methods your application uses, not only a SELECT of the primary key. Include value extraction, existence checks, and full document retrieval.
Those operations exercise different paths. Keep parameter values and the chosen indexes consistent across each comparison.
Turn on STATISTICS IO and TIME for a controlled test. Read the messages produced by your server. Don't substitute another system's measurements for your own.
Check elapsed time alongside CPU time because blocking changes the first without explaining the second. Repeat representative requests and keep the resulting plans with the measurements.
A document that fits neatly on disk still needs processing when queried. Smaller storage doesn't fix a badly shaped XML predicate. If an application retrieves every document before filtering, storage savings won't repair that design.
Review the query and the storage choice together. They share resources, even though they solve different problems.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT DocumentId, Payload.value('(/document/item/name/text())[1]', 'nvarchar(50)') AS ItemName
FROM dbo.XmlCompressionDemo
WHERE Payload.exist('/document/item[name = "Sample"]') = 1;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Explain What the Space Result Cannot Say
A reduced allocation doesn't automatically shrink the database file on the volume. Freed space inside a file becomes available for reuse. Don't follow a successful compression test with routine shrinking.
That adds another costly operation and changes the test you were trying to understand. Leave the file sizing discussion separate.
Backups introduce another layer. Their compression settings and the rest of the database affect backup size. Measure a representative backup if that is your goal.
The table's before-and-after report doesn't predict the exact backup change. A smaller drawer doesn't tell you the weight of the whole moving truck.
What matters more on this server, disk pressure or CPU headroom? That answer decides whether the trade is useful. Include peak ingestion and document updates in the test.
Read-only reports alone hide write costs. A successful setting has to work across the load that keeps the application running.
Keep a Clear Path to Reverse XML Compression
Record the original partition settings before enabling the option. Turning it off also requires rewriting storage. Budget the reversal like another rebuild, including disk space and logging.
Don't describe rollback as an instant switch. The syntax is reversible, but the physical work still has to finish.
Choose the table based on representative documents and measured pressure. Keep the same rows for both measurements. Review every XML index and the CPU evidence before deciding.
That gives you a reason to enable the feature, or a reason to leave it alone. Either answer is useful when it comes from your server.
Save the scripts and the reports with the table name and test conditions. Revisit the choice when document shapes or access patterns change. A setting that fits archival documents needs another review for frequent updates.
Storage choices age along with workloads. The goal is a supported trade, with evidence you can explain.
Related reading on this blog: Row and Page Compression: Estimating Savings Before You Compress and SQL SERVER Performance: JSON vs XML.
XML compression is not a free space promise, it is a storage trade you can measure.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
