高估查詢導致性能不佳 - 帶有 xml 處理的 TVF

今天我問了關於查詢性能不好的問題。該查詢在很多視圖上有很多連接。我想我發現哪個視圖對 SQL Server 來說是個麻煩。這是在該視圖上執行的實際查詢計劃https://www.brentozar.com/pastetheplan/?id=BJB4WOV6S如您所見，SQL Server 高度高估了行數和所需記憶體。外部應用用於執行返回已處理 XML 的 TVF，xml 非常小。我想知道如何避免這種高估（以及是什麼原因造成的）？我應該為我的表創建 xml 索引嗎？

TVF FUNCTION [schema].[functionname](@xmlData XML) RETURNS TABLE AS return SELECT Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN FROM @xmlData.nodes('PI') Tbl(Col) GO 範例 之一declare @para as xml set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>' Select * from [schema].[functionname](@para)

這裡的第一部分是消除對 TVF 的高估計。

測試數據：

declare @para as xml 
set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>'

CREATE TABLE dbo.Test(id int identity(1,1) primary key not null, val int, xmldata xml)

INSERT INTO dbo.Test WITH(TABLOCK)(val,xmldata)
SELECT TOP(400000) 
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
@para
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2

使用的主索引

CREATE PRIMARY XML INDEX Test_xmlData  
   ON dbo.Test(xmldata);  

查詢重寫 #1：

SELECT t.*, Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN
FROM dbo.Test t
OUTER APPLY t.xmldata.nodes('PI') as Tbl(Col);

第一部分刪除了此處找到的高估計執行計劃：

沒有xml索引的原圖，高估

使用 xml 索引進行計劃，降低估計

查詢重寫 #2：

我們可以將查詢簡化為：

SELECT t.*, xmldata.value('(PI/PN)[1]', 'nvarchar(32)') as PPN
FROM dbo.Test t;

索引#2，選擇性索引：

然後可以創建一個選擇性索引：

CREATE SELECTIVE XML INDEX sxi_index
ON dbo.Test(xmldata)
FOR (
pathPIPN= 'PI/PN' AS SQL NVARCHAR(32) 
);

最終執行統計：

SQL Server Execution Times:
  CPU time = 2109 ms,  elapsed time = 11066 ms.

與原始查詢的查詢統計資訊相比，CPU 時間要低約 3 倍：

SELECT *
FROM dbo.Test 
OUTER APPLY [dbo].[functionname](xmldata);

SQL Server Execution Times:
  CPU time = 6000 ms,  elapsed time = 10196 ms.

以及簡化查詢和選擇性索引的查詢計劃：

估計值與實際值匹配。

查詢計劃

外部應用解決方案也可以使用選擇性索引：

SELECT t.*, Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN
FROM dbo.Test t
OUTER APPLY t.xmldata.nodes('PI') as Tbl(Col);

在這種情況下，cpu 時間為 3.5 秒：

CPU time = 3594 ms,  elapsed time = 10979 ms.

在我的例子中。

引用自：https://dba.stackexchange.com/questions/254728