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:


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

    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:


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

INSERT INTO [dbo].[##target_2994] /*******/
        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.


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.

  • We tested on a 2008 RTM instance and it worked fine... I will try changing the compatibility level to 90 and see if that makes a difference. – Jon Seigel Oct 19 '12 at 17:52
  • Changing the compatibility level didn't help. – Jon Seigel Oct 19 '12 at 18:20
  • 2
    OPTION(OPTIMIZE FOR(...)) did not work; changing the XPath query to use // at the beginning did not work; using (element/text())[1] solved the problem. Thanks! – Jon Seigel Oct 19 '12 at 18:53
  • 1
    @JonSeigel - You can also use Trace Flag 4130 if it is the same issue as per the Connect item. I am beginnining to doubt it though as in the issue there the plans are not the same. The slow one gets an unnecessary eager spool to the plan. – Martin Smith Oct 20 '12 at 9:25
  • 1
    It's still really unclear what the actual problem is because we could not reproduce it in our QA and dev environments which have nearly identical configurations. Honestly, having spent close to 6 hours on this problem, I'm just happy to have found any solution. – Jon Seigel Oct 20 '12 at 15:59

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.

  • The schema between the two databases for each customer is identical (they have to be the same version for updates). We backup/restored some of the data sets from hosting into our QA environment for testing, and it worked fine there. – Jon Seigel Oct 19 '12 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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