Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for select run on that view https://www.brentozar.com/pastetheplan/?id=BJB4WOV6S As you can see SQL Server highly overestimates number of rows and needed memory. Outter apply is used to run TVF that is returning processed XML, xmls are rather very small. I wonder how to avoid such overestimation(and what cuase them)? Should i create xml indexes for my table?
One of the TVF
FUNCTION [schema].[functionname](@xmlData XML)
SELECT Tbl.Col.value('PN', 'nvarchar(32)') as PPN
FROM @xmlData.nodes('PI') Tbl(Col)
declare @para as xml
set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>'
Select * from [schema].[functionname](@para)