Tuning a query to parse XML data on SQL Server 2014
<p>I have a table on a SQL Server 2014 database that stores auditing information for record changes in a <code>VARCHAR(MAX)</code> column (poor man's CDC.)</p>

<p>This data is in following format:</p>

<pre><code>&lt;span class="fieldname"&gt;Assigned To&lt;/span&gt;
   changed from &lt;span class="oldvalue"&gt;user1&lt;/span&gt;
   to &lt;span class="newvalue"&gt;user2&lt;/span&gt;&lt;br /&gt;
&lt;span class="fieldname"&gt;Status&lt;/span&gt;
   changed from &lt;span class="oldvalue"&gt;QA&lt;/span&gt;
   to &lt;span class="newvalue"&gt;Development&lt;/span&gt;&lt;br /&gt;
&lt;span class="fieldname"&gt;Progress&lt;/span&gt;
   changed from &lt;span class="oldvalue"&gt;Yes&lt;/span&gt;
   to &lt;span class="newvalue"&gt;No&lt;/span&gt;&lt;br /&gt;
...
</code></pre>

<p>I need to parse that information in order to retrieve the data transposed so that it looks like so:</p>

<pre><code>Record    FieldName      OldValue   NewValue
------    ---------      --------   --------
1234      Assigned To    user1      user2
1234      Status         QA         Development
1234      Progress       Yes        No
</code></pre>

<p>The stored procedure attempts to do this by converting the data to XML and then using XPath retrieving the necessary pieces:</p>

<pre><code>;WITH TT AS (
   SELECT TransId,
      CAST('&lt;root&gt;&lt;rec&gt;' + REPLACE(REPLACE(TransDescription, 'Ticket reopened... Status', 'Status'), '&lt;br /&gt;', '&lt;/rec&gt;&lt;rec&gt;') + '&lt;/rec&gt;&lt;/root&gt;' AS XML) TransXml
   FROM dbo.Trans
   WHERE TransDate &gt;= '11/1/2016'
      AND (TransDescription LIKE '%Ticket reopened... Status%' OR TransDescription LIKE '%Status%'))
SELECT TransId,
   TransXml,
   FieldName = T.V.value('span[@class="fieldname"][1]', 'varchar(255)'),
   OldValue = NULLIF(T.V.value('span[@class="oldvalue"][1]', 'varchar(255)'), 'nothing'),
   NewValue = NULLIF(T.V.value('span[@class="newvalue"][1]', 'varchar(255)'), 'nothing')
INTO #tmp
FROM TT
   CROSS APPLY TT.TransXml.nodes('root/rec') T(V);
</code></pre>

<p>Here is the execution plan: <a href="https://www.brentozar.com/pastetheplan/?id=rJF2GRB7g" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=rJF2GRB7g</a></p>

<p>The corresponding IO stats:</p>

<pre><code>Table 'Trans'. Scan count 9, logical reads 27429, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2964994, physical reads 0, read-ahead reads 0, lob logical reads 2991628, lob physical reads 0, lob read-ahead reads 0.
</code></pre>

<p>This query is excruciatingly slow (the example was for just 10 days' worth of data,) and gets progressively slower with more data.</p>

<p>What are my options for tuning this query?</p>