Overestimated query resulting in poor performance - TVF with xml processing 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) 
                RETURNS  TABLE
            AS
            return
                SELECT Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN
                    FROM   @xmlData.nodes('PI') Tbl(Col)
GO
example

declare @para as xml 
set @para = '&lt;PI&gt;&lt;PGID&gt;00000000000000000000000000000000&lt;/PGID&gt;&lt;PN&gt;0&lt;/PN&gt;&lt;/PI&gt;'
Select * from [schema].[functionname](@para)
I think the base cardinality estimate for XML TVF is 10,000 rows - I read it in a presentation by Michael Rys

To improve estimates, you can either add a schema to your XML or create an XML index.

Other than that I could only direct you to row goal hints and estimates trick I first heard from Adam Machanic here