Sql-server – Fast XML, slow XML

performancesql serversql-server-2008sql-server-2008-r2xml

Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data.

The process works by selecting a chunk of rows from the source as XML, which is passed through the application to the target server, where it's shredded into a global temp table. (The source and target databases can be on 2 different instances.) That process repeats until all the data required is in the temp table on the target instance. Finally, the temp table records are consolidated into the actual target database table.

The problem we're having is that in some situations, the second chunk is extremely slow, with very high CPU usage, and it just goes nowhere. We're able to reproduce the issue in our hosting environment, but not in dev or QA. Some of our clients are having this problem, too — one of them let it run overnight and finally killed it the next morning after running for 18 (!) hours. In that case, I'm not sure how far along it got; I can't get past the second chunk in hosting after waiting for ~2 hours.

This is the statement batch for the first chunk:

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

SELECT
    n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
    n.x.value(N'field2[1]', 'smallint') AS field2,
    ... (8 more fields of various types) ...
    INTO [##target_2994] /*******/
    FROM @src.nodes('Rows[1]/Row') n(x);

And this is the batch for the second and subsequent chunks, which is the problem:

SET NOCOUNT ON;

DECLARE @src xml;
SET @src = CAST(@P1 AS xml);

INSERT INTO [dbo].[##target_2994] /*******/
    SELECT
        n.x.value(N'field1[1]', 'uniqueidentifier') AS field1,
        n.x.value(N'field2[1]', 'smallint') AS field2,
        ... (8 more fields of various types) ...
        FROM @src.nodes('Rows[1]/Row') n(x);

Here's what I've looked at so far:

It's not a blocking issue: wait stats are 99% SOS_SCHEDULER_YIELD on the INSERT statement.
sys.dm_io_virtual_file_stats on the target tempdb shows it's basically idle, so it's not an I/O issue.
All the data has only fixed-width columns, so no massively long text fields.
Data chunk size is currently 25,000 rows, and we might lower this, but it doesn't explain the difference because we've tested with some of the same data sets just fine. The biggest table needed to be transferred is ~725,000 rows, which tested fine.
The query plans are identical* between problem vs. no problem (I did a file compare of the XML).
The session SET options are the same between problem vs. no problem.
Version doesn't seem to be a factor: hosting is 2008 R2 SP1 Enterprise x64; we've tested with no issues on 2005 SP4+ Standard x64 all the way to 2008 R2 SP1+ Developer x86. Customers having issues are 2008 RTM/SP1 Standard/Enterprise x64 (so far).
Virtualization doesn't seem to be a factor: hosting and QA is virtualized; dev is partly virtualized; customers having issues are physical. MAXDOP isn't set for any of our servers (max = 4 logical processors); I'm not sure about the customers' settings.
The two databases being on the same server vs. different server makes no difference.
Running the update application on a TS box vs. locally makes no difference.
Tempdb database settings are identical.
Instance and database collations are identical.
Changing the compatibility level of tempdb on the target server to 90 didn't help. (Per Mark's answer)
There are no significat instance configuration differences. (Per Jimbo's answer)

Can anyone suggest other things to look at?

_{* The calculated expressions were named differently, and there was a < 1% difference in one of the estimated row sizes, but everything else was the same, including the overall cost estimates.}

Best Answer

There is a comparable issue logged on connect for the version you're having a problem with - An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1.

Using SQL2008, when shredding XML using the nodes query, the performance is fine when just SELECTing. However, when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.

Related Solutions

Sql-server – Slow temp table drops in sql 2005

You didn't mention the IOPS used by the tempdb LUN. Are you seeing any IO waits on the LUN device? Is anything else on that LUN? Is another system using the disk group/pool that the LUN is carved from?

Ditto master db -- same q's.

Also, are you using local temp tables (#tablename) or global temp tables (##tablename)? If global could another SPID be keeping the shared lock on the global temp tables when you're trying to delete them?

SQL Server – Optimizing Plans with XML Readers

The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is:

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar operators are different from other operators (SQL Server 2005 onward) in that the expression(s) they define are not necessarily evaluated at the position they appear in the visible execution plan; evaluation can be deferred until the result of the computation is required by a later operator.

In the present query, the target_data string is typically large, making the conversion from string to XML expensive. In slow plans, the string to XML conversion is performed every time a later operator that requires the result of Expr1000 is rebound.

Rebinding occurs on the inner side of a nested loops join when a correlated parameter (outer reference) changes. Expr1000 is an outer reference for most of the nested loops joins in this execution plan. The expression is referenced multiple times by several XML Readers, both Stream Aggregates, and by a start-up Filter. Depending on the size of the XML, the number of times the string is converted to XML can easily number in the millions.

The call stacks below show examples of the target_data string being converted to XML (ConvertStringToXMLForES - where ES is the Expression Service):

Start-up Filter

Start-up Filter call stack

XML Reader (TVF Stream internally)

TVF Stream call stack

Stream Aggregate

Stream Aggregate call stack

Converting the string to XML each time any of these operators rebind explains the performance difference observed with the nested loops plans. This is regardless of whether parallelism is used or not. It just so happens that the optimizer chooses a hash join when the MAXDOP 1 hint is specified. If MAXDOP 1, LOOP JOIN is specified, performance is poor just as with the default parallel plan (where the optimizer chooses nested loops).

How much performance increases with a hash join depends on whether Expr1000 appears on the build or probe side of the operator. The following query locates the expression on the probe side:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

I have reversed the written order of the joins from the version shown in the question, because join hints (INNER HASH JOIN above) also force the order for the whole query, just as if FORCE ORDER had been specified. The reversal is necessary to ensure Expr1000 appears on the probe side. The interesting part of the execution plan is:

hint 1

With the expression defined on the probe side, the value is cached:

Hash Cache

Evaluation of Expr1000 is still deferred until the first operator needs the value (the start-up filter in the stack trace above) but the computed value is cached (CValHashCachedSwitch) and reused for later calls by the XML Readers and Stream Aggregates. The stack trace below shows an example of the cached value being reused by an XML Reader.

Cache reuse

When the join order is forced such that the definition of Expr1000 occurs on the build side of the hash join, the situation is different:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st 
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Hash 2

A hash join reads its build input completely to construct a hash table before it starts probing for matches. As a result, we have to store all the values, not just the one per thread being worked on from the probe side of the plan. The hash join therefore uses a tempdb work table to store the XML data, and every access to the result of Expr1000 by later operators requires an expensive trip to tempdb:

Slow access

The following shows more details of the slow access path:

Slow details

If a merge join is forced the input rows are sorted (a blocking operation, just like the build input to a hash join) resulting in a similar arrangement where slow access via a tempdb sort-optimized worktable is required because of the size of the data.

Plans that manipulate large data items can be problematic for all sorts of reasons that are not apparent from the execution plan. Using a hash join (with the expression on the correct input) is not a good solution. It relies on undocumented internal behaviour with no guarantees it will work the same way next week, or on a slightly different query.

The message is that XML manipulation can be tricky things to optimize today. Writing the XML to a variable or temporary table before shredding is a much more solid workaround than anything shown above. One way to do this is:

DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs 
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE 
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Finally, I just want to add Martin's very nice graphic from the comments below:

Martin's graphic

Best Answer

Related Solutions

Sql-server – Slow temp table drops in sql 2005

SQL Server – Optimizing Plans with XML Readers

Related Question