In SQL Server, I have a stored procedure with XML type temporary variable, and I am doing a delete operation on that variable. When I am running this stored procedure in my local VM which has 4 cores and 6 GB RAM, it takes 24 seconds to execute. But when I am running the same stored procedure in server with 40 cores and 128 GB RAM, this delete statement is taking more than 38 minutes to execute. The whole stored procedure gets hanged at this delete statement for 38 minutes. After commenting out the delete statement, the stored procedure executes in 8 seconds on the server. How I can fix this performance issue. Is there something wrong in ther SQL server configuration?
在SQL Server中，我有一个带有XML类型临时变量的存储过程，我正在对该变量执行删除操作。当我在我的本地VM运行这个存储过程时，它有4个内核和6gb RAM，执行起来需要24秒。但是当我在服务器上运行同样的存储过程时，有40个内核和128 GB内存，这个delete语句的执行时间超过了38分钟。整个存储过程在此删除语句处挂起38分钟。在注释掉delete语句之后，存储过程将在服务器上执行8秒。如何修复这个性能问题。SQL server配置有问题吗?
DECLARE @PaymentData AS XML
SET @PaymentData = .....(Main XML Query)
SET @PaymentData.modify('delete //*[not(node())]')
@Mikael: Below is the execution plan for shredding into rows solution on server (with 40 cores and 128 GB RAM) 
And Below is the excecution plan in my local VM(with 4 cores and 6 GB RAM): 
@Mikael:下面是服务器上将行分解为行解决方案的执行计划(40个内核，128 GB RAM)，下面是我本地VM中的执行计划(4个内核，6gb RAM):
1 个解决方案
#1
7
On my machine the delete took 1 hour 25 minutes and gave me this not so pretty query plan.
在我的机器上，删除操作花费了1小时25分钟，给了我这个不太好的查询计划。
This plan finds all empty nodes (the ones to be deleted) and stores those in a Table Spool. Then for each node in the entire document there is a check if that node is present in the spool (Nested Loops (Left Semi Join)) and if it is that node is excluded from the final result (Merge join (Left Anti Semi Join)). The xml is then rebuilt from the nodes in the UDX operator and assigned to the variable. The table spool is not indexed so for each node that needs to be checked there will be a scan of the entire spool (or until a match is found).
该计划查找所有空节点(要删除的节点)并将它们存储在一个表线轴中。然后对整个文档中的每个节点进行检查，检查该节点是否存在于假脱机(嵌套循环(左半脱机)中，如果该节点被排除在最终结果之外(合并连接(左反半脱机连接))。然后从UDX操作符中的节点重新构建xml并分配给变量。表假脱机没有被索引，因此对于需要检查的每个节点，将会扫描整个假脱机(或者直到找到匹配)。
That essentially means the performance of this algorithm is O(n*d) where n is the total number of nodes and d is the total number or deleted nodes.
这实质上意味着该算法的性能是O(n*d)，其中n为节点总数，d为节点总数或被删除节点数。
There are a couple of possible workarounds.
有几个可能的变通方法。
First and perhaps the best is if you could modify your XML query to not produce the empty nodes in the first place. Totally possible if you create the XML with for xml and perhaps not possible if you already have parts of the XML stored in a table.
首先，也许最好的方法是修改XML查询，使其不会首先生成空节点。如果您使用XML创建XML，那么这是完全可能的;如果您已经在表中存储了部分XML，那么这是不可能的。
Another option is to shred the XML on Row (see sample XML below), put the result in a table variable, modify the XML in the table variable and then recreate the combined XML.
另一种选择是将XML分解为行(参见下面的示例XML)，将结果放入表变量中，修改表变量中的XML，然后重新创建组合的XML。
declare @T table(PaymentData xml);
insert into @T
select T.X.query('.')
from @PaymentData.nodes('Row') as T(X);
update @T
set PaymentData.modify('delete //*[not(node())]');
select T.PaymentData as '*'
from @T as T
for xml path('');
This will give you the performance characteristic of O(n*s*d) where n is the number of row nodes, s is the number of sub-nodes per row node and d is the number of deleted rows per row node.
这将给出O(n*s*d)的性能特征，其中n是行节点的数量，s是每个行节点的子节点数量，d是每个行节点删除的行数。
A third option that I really can't recommend is to use an undocumented trace flag that removes the use of a spool in the plan. You can try it out in test or you can perhaps capture the plan generated and use it in a plan guide.
我真的不推荐的第三种选择是使用一个没有文档说明的跟踪标志，它可以消除计划中对spool的使用。您可以在测试中尝试它，或者您可以捕获生成的计划并在计划指南中使用它。
declare @T table(PaymentData xml);
insert into @T values(@PaymentData);
update @T
set PaymentData.modify('delete //*[not(node())]')
option (querytraceon 8690);
select @PaymentData = PaymentData
from @T;
Query plan with trace flag:
带有跟踪标志的查询计划:
Instead of 1 hour 25 minutes, this version took 4 seconds on my computer.
这个版本在我的电脑上花了4秒，而不是1小时25分钟。
Shredding the XML to multiple rows to the table variable took in total 6 seconds to execute.
将XML分解到表变量的多个行总共需要6秒的时间。
Not having to delete any rows at all is of course the fastest.
不需要删除任何行当然是最快的。
Sample data, 12000 nodes with 32 subnodes where 2 is empty if you want to try this at home.
样本数据，12000个节点，32个子节点，其中2为空，如果你想在家里尝试的话。
declare @PaymentData as xml;
set @PaymentData = (
select top(12000)
1 as N1, 1 as N2, 1 as N3, 1 as N4, 1 as N5, 1 as N6, 1 as N7, 1 as N8, 1 as N9, 1 as N10,
1 as N11, 1 as N12, 1 as N13, 1 as N14, 1 as N15, 1 as N16, 1 as N17, 1 as N18, 1 as N19, 1 as N20,
1 as N21, 1 as N22, 1 as N23, 1 as N24, 1 as N25, 1 as N26, 1 as N27, 1 as N28, 1 as N29, 1 as N30,
'' as N31,
'' as N32
from sys.columns as c1, sys.columns as c2
for xml path('Row')
);
Note: I have no idea why it only took 24 seconds to execute on one of your servers. I would advise you to recheck that the XML actually is identical. Or why not test using the XML sample I have provided for you.
注意:我不知道为什么在您的一个服务器上执行只需要24秒。我建议您重新检查XML实际上是相同的。或者为什么不使用我为您提供的XML示例进行测试呢?
Update:
更新:
For the shredding version the problem with the spool in the delete query could be moved to the shredding query instead leaving you with about the same bad performance. That is however not always true. I have seen plans where there is no spool and plans where there are a spool and I don't know why it is there sometimes and why it is not at other times.
对于分解版本，删除查询中的假脱机的问题可以转移到分解查询，而不是留给您相同的糟糕性能。然而，这并不总是正确的。我见过没有线轴的计划，也见过有线轴的计划，我不知道为什么有时候有线轴，为什么有时候没有线轴。
I have also found that if you use a temp table instead with insert ... into I don't get the spool in the shredding query.
我还发现，如果您使用临时表而不是插入…在分解查询中，我没有得到线轴。
select T.X.query('.') as PaymentData
into #T
from @PaymentData.nodes('Row') as T(X);
update #T
set PaymentData.modify('delete //*[not(node())]');
#1
7
On my machine the delete took 1 hour 25 minutes and gave me this not so pretty query plan.
在我的机器上，删除操作花费了1小时25分钟，给了我这个不太好的查询计划。
This plan finds all empty nodes (the ones to be deleted) and stores those in a Table Spool. Then for each node in the entire document there is a check if that node is present in the spool (Nested Loops (Left Semi Join)) and if it is that node is excluded from the final result (Merge join (Left Anti Semi Join)). The xml is then rebuilt from the nodes in the UDX operator and assigned to the variable. The table spool is not indexed so for each node that needs to be checked there will be a scan of the entire spool (or until a match is found).
该计划查找所有空节点(要删除的节点)并将它们存储在一个表线轴中。然后对整个文档中的每个节点进行检查，检查该节点是否存在于假脱机(嵌套循环(左半脱机)中，如果该节点被排除在最终结果之外(合并连接(左反半脱机连接))。然后从UDX操作符中的节点重新构建xml并分配给变量。表假脱机没有被索引，因此对于需要检查的每个节点，将会扫描整个假脱机(或者直到找到匹配)。
That essentially means the performance of this algorithm is O(n*d) where n is the total number of nodes and d is the total number or deleted nodes.
这实质上意味着该算法的性能是O(n*d)，其中n为节点总数，d为节点总数或被删除节点数。
There are a couple of possible workarounds.
有几个可能的变通方法。
First and perhaps the best is if you could modify your XML query to not produce the empty nodes in the first place. Totally possible if you create the XML with for xml and perhaps not possible if you already have parts of the XML stored in a table.
首先，也许最好的方法是修改XML查询，使其不会首先生成空节点。如果您使用XML创建XML，那么这是完全可能的;如果您已经在表中存储了部分XML，那么这是不可能的。
Another option is to shred the XML on Row (see sample XML below), put the result in a table variable, modify the XML in the table variable and then recreate the combined XML.
另一种选择是将XML分解为行(参见下面的示例XML)，将结果放入表变量中，修改表变量中的XML，然后重新创建组合的XML。
declare @T table(PaymentData xml);
insert into @T
select T.X.query('.')
from @PaymentData.nodes('Row') as T(X);
update @T
set PaymentData.modify('delete //*[not(node())]');
select T.PaymentData as '*'
from @T as T
for xml path('');
This will give you the performance characteristic of O(n*s*d) where n is the number of row nodes, s is the number of sub-nodes per row node and d is the number of deleted rows per row node.
这将给出O(n*s*d)的性能特征，其中n是行节点的数量，s是每个行节点的子节点数量，d是每个行节点删除的行数。
A third option that I really can't recommend is to use an undocumented trace flag that removes the use of a spool in the plan. You can try it out in test or you can perhaps capture the plan generated and use it in a plan guide.
我真的不推荐的第三种选择是使用一个没有文档说明的跟踪标志，它可以消除计划中对spool的使用。您可以在测试中尝试它，或者您可以捕获生成的计划并在计划指南中使用它。
declare @T table(PaymentData xml);
insert into @T values(@PaymentData);
update @T
set PaymentData.modify('delete //*[not(node())]')
option (querytraceon 8690);
select @PaymentData = PaymentData
from @T;
Query plan with trace flag:
带有跟踪标志的查询计划:
Instead of 1 hour 25 minutes, this version took 4 seconds on my computer.
这个版本在我的电脑上花了4秒，而不是1小时25分钟。
Shredding the XML to multiple rows to the table variable took in total 6 seconds to execute.
将XML分解到表变量的多个行总共需要6秒的时间。
Not having to delete any rows at all is of course the fastest.
不需要删除任何行当然是最快的。
Sample data, 12000 nodes with 32 subnodes where 2 is empty if you want to try this at home.
样本数据，12000个节点，32个子节点，其中2为空，如果你想在家里尝试的话。
declare @PaymentData as xml;
set @PaymentData = (
select top(12000)
1 as N1, 1 as N2, 1 as N3, 1 as N4, 1 as N5, 1 as N6, 1 as N7, 1 as N8, 1 as N9, 1 as N10,
1 as N11, 1 as N12, 1 as N13, 1 as N14, 1 as N15, 1 as N16, 1 as N17, 1 as N18, 1 as N19, 1 as N20,
1 as N21, 1 as N22, 1 as N23, 1 as N24, 1 as N25, 1 as N26, 1 as N27, 1 as N28, 1 as N29, 1 as N30,
'' as N31,
'' as N32
from sys.columns as c1, sys.columns as c2
for xml path('Row')
);
Note: I have no idea why it only took 24 seconds to execute on one of your servers. I would advise you to recheck that the XML actually is identical. Or why not test using the XML sample I have provided for you.
注意:我不知道为什么在您的一个服务器上执行只需要24秒。我建议您重新检查XML实际上是相同的。或者为什么不使用我为您提供的XML示例进行测试呢?
Update:
更新:
For the shredding version the problem with the spool in the delete query could be moved to the shredding query instead leaving you with about the same bad performance. That is however not always true. I have seen plans where there is no spool and plans where there are a spool and I don't know why it is there sometimes and why it is not at other times.
对于分解版本，删除查询中的假脱机的问题可以转移到分解查询，而不是留给您相同的糟糕性能。然而，这并不总是正确的。我见过没有线轴的计划，也见过有线轴的计划，我不知道为什么有时候有线轴，为什么有时候没有线轴。
I have also found that if you use a temp table instead with insert ... into I don't get the spool in the shredding query.
我还发现，如果您使用临时表而不是插入…在分解查询中，我没有得到线轴。
select T.X.query('.') as PaymentData
into #T
from @PaymentData.nodes('Row') as T(X);
update #T
set PaymentData.modify('delete //*[not(node())]');