XML column performance in TSQL

Question

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries call for the column (usually single row requests), most do not. Is there a significant performance benefit to breaking that xml column out to its own linked table?

Answer 1

The database engine is pretty smart about this and does not read LOB pages unless it has to as far as I can tell, but I created a test-rig to double-check.

Test Rig

USE master
GO

SET NOCOUNT ON
GO

CREATE DATABASE bigXMLTest
GO
ALTER DATABASE bigXMLTest SET RECOVERY SIMPLE
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest', SIZE = 20GB , FILEGROWTH = 128MB )
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest_log', SIZE = 512MB )
GO


USE bigXMLTest
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
GO

CREATE TABLE dbo.largeTable (

    rowId INT IDENTITY PRIMARY KEY,

    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    someXML     XML NOT NULL
)
GO


-- Add dummy data
;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( someDate, someData, someXML )
SELECT
    DATEADD( day, rn % 333, '1 Jan 2016' ), 
    NEWID(), 
    ( SELECT TOP 100 * FROM sys.messages WHERE language_id = 1033 FOR XML PATH, ROOT('root'), TYPE )    -- creates 24k size piece of XML on my machine
FROM cte

CHECKPOINT
GO 10

EXEC sp_spaceused 'dbo.largeTable'
GO

This creates a test table with 1 million rows, the non-XML data around 36MB and the XML data about 23GB.

Test Queries

--DROP INDEX _udx ON dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

-- Add covering index for the query
CREATE UNIQUE INDEX _udx ON dbo.largeTable ( rowId ) INCLUDE ( someDate, someData )

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

Test Results

Analysis

Query 1: A select *... causes a full table scan, so the database engine does a high number of LOB reads to read the XML which is stored out of row in LOB pages. XML can be stored in-row if it's small enough, but the default is out-of-row. This query takes 5 minutes, ie if you are reading LOB pages on a table like this, you will know about it.

Query 2 A select with column list not including the XML column, has the same number of logical reads in the top window (11,154), but no LOB reads. The query takes less than a second.

Query 3: After the non-clustered index has been added, a select * still causes a full table scan as expected. This query behaves the same as Query 1.

Query 4: After the non-clustered index has been added, it covers the select with column list query so there is a non-clustered index scan with no LOB reads and lower logical reads overall.

The LOB Reads looks like a broad categorisation as the XML storage pages are classed as TEXT_MIX_PAGE in the sys.dm_db_database_page_allocations DMV:

In summary, it looks like there would be little benefit to splitting the same as the database engine can differentiate when and when not to perform LOB reads in this admittedly simple test rig. See here for some further details.

Answer 2

The context of this question is strikingly similar to this question: LOB_DATA, slow table scans, and some I/O questions. But the direction is a bit different.

In order to answer whether or not there would be much benefit in moving the XML column into its own table requires answering the following questions first (and I will explain why in a moment):

1. How many rows are in this table?
2. Are there any indexes on this table?
3. If there are indexes, does the 36 GB value represent the Reserved space, the Data space, or Data and Indexes?
4. How (exactly) did you determine that the table is only 36 MB without the XML column?
5. What is the exact DDL for the table, including any indexes?

Why does that information mater? Well, let's assume that there are 1 million rows, and that the XML and non-XML data is distributed fairly evenly. That means that each row is approximately 36 bytes of non-XML data and 36,000 bytes of XML data. However, there is more going on here than is apparently obvious.

The way that XML / LOB data works is that:

• if the size is <= 8000 bytes, and there is room on the data page, then it will be stored in-row,
• if the size is <= 8000 bytes but there isn't room on the data page, OR if the size is between 8001 and 42,000 bytes, then the data will be stored in 1 - 5 LOB pages while the pointers to those LOB pages are stored in row (hence no additional hop to a separate mapping page). This is important because the pointers have a minimum size of 24 bytes for the first one, and then 12 additional bytes each for pointers 2 - 5.
• for LOB / XML data over 42,000 bytes, or when there isn't room in row to store the 36 - 72 extra bytes for the pointers, then there will be a single 24 byte pointer in row pointing to a mapping page (called the TEXT_TREE) that has the pointers to the actual LOB pages (hence the additional hop to find the LOB pointer in the TEXT_TREE page(s)).

In your scenario there is 36,000 bytes per row of XML data. That is under the 42,000 byte limit where the data is forced to go through the TEXT_TREE mapping page leaving only a 24 byte pointer in row. In your case, it will allocate either 4 or 5 LOB pages and add the pointers to the in-row data (because it is faster read access than going through the extra hop to the TEXT_TREE mapping page(s)). This means that the in-row cost of having the XML data off-row is either 60 or 72 bytes. And with only 36 other bytes in the row, there is plenty of space for that 60 or 72 bytes.

And so this is why getting those questions above answered is important: it could very well be that having the XML column in this table is doubling the size of each row. Assuming a 100% FILLFACTOR and an 8060 byte data page, that equates to approx 111 data pages for the non-XML data, but 223 data pages when including the pointers for the XML data.

What the precise benefit of reducing 223 data pages down to 111 pages can only be determined by testing on your system to see the actual effect. It might be too minimal to be worth the effort of breaking the XML field out. But maybe it is worth it, especially if your system experiences memory pressure often enough, in which case data pages get evicted from the Buffer Pool. Taking up half as much space in memory could mean a longer Page Life Expectancy for these pages, and probably other data pages.

But if memory isn't an issue, and if saving only 112 pages provides no increase in performance, then at least you know how the underlying mechanisms work so that you know how to make the decision. Especially if / when the assumptions change (or for other readers who have slightly different setups, such as XML data that is over 42,000 bytes and hence only 24 bytes in row and/or much more than 36 bytes of in-row data).

I have some test scripts on PasteBin, if you want to walk through how LOBs are handled:

• T-SQL script to test LOB allocations and reads
• T-SQL script to test various scenarios for LOB reads