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', 'uniqueidentifier') AS field1, n.x.value(N'field2', 'smallint') AS field2, ... (8 more fields of various types) ... INTO [##target_2994] /*******/ FROM @src.nodes('Rows/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', 'uniqueidentifier') AS field1, n.x.value(N'field2', 'smallint') AS field2, ... (8 more fields of various types) ... FROM @src.nodes('Rows/Row') n(x);
Here's what I've looked at so far:
- It's not a blocking issue: wait stats are 99%
sys.dm_io_virtual_file_statson the target
tempdbshows 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
SEToptions 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.
MAXDOPisn'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.