Suggestion to improve XML shredding performance about darlingdata

[erikdarlingdata]

Just to make sure I fully understand: what's the advantage of assigning the XML to a variable up front, and then shredding? Does something weird happen in the DMV query that could lead to extra XML-shredding before the target_name filter is applied?

[micke314]

You mean instead of shredding on the value converted to xml from nvarchar(max) in the DMV. That is blogged about here.

https://www.sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html

The cast to XML is deferred to where ever the query needs the XML, for each row, for each value and when the cast is done the XML is validated over and over again.

So by casting to a variable the cast happens only once.

[erikdarlingdata]

Oh wow, that's badass! Thanks. I wonder if I could use that in sp_BlitzLock too.

[micke314]

It does not of course have to be a variable to fix this, it could as well have been to a temp table with an XML column.

You will have the exact same issue if you shred queryplans from the query store.
Dump the querystore to a temp table with an XML column and shred the temp table instead.
Will probably be much faster.

[erikdarlingdata]

@micke314 works awesome! Thank you!