Fast XML, slow XML

Question

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.}

Answer 1

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.

Answer 2

Compare databases using something like SQL Compare from RedGate

Compare data in tables using something like SQL Data Compare from Redgate

If the schema is identical and data differences are not significant then compare the SQL Instance properties.

If differences are not significant then look at your temp file usage.

You are inserting into a global table - try inserting into an actual table that your create.