I ran into something interesting and I'm not really sure what happened.

Code that has been running with no problem suddenly started to have performance impacts. When I took a look at the query's execution plan, I found that the expenses were all with a chunk of XML.  This stored procedure called another one (code reuse is good) that returned XML. I then queried that XML block a couple of times using variations on:

SELECT Tbl.Col.value('MediaID[1]', 'bigint'),Tbl.Col.value('FolderPath[1]', 'nvarchar(2048)') FROM  @MediaListXML.nodes('//assets') Tbl(Col)

For some reason, this took a long time to process, even with only three simple items in the XML. By changing the code to store the relevant columns in a table variable:

DECLARE @MediaList table(MediaID bigint,FolderPath nvarchar(2048))
INSERT INTO @MediaList select Tbl.Col.value('MediaID[1]', 'bigint'),Tbl.Col.value('FolderPath[1]', 'nvarchar(2048)')
          FROM  @MediaListXML.nodes('//assets') Tbl(Col)

For whatever reason, replacing the multiple queries of the XML that had been performing perfectly fine for months before this with the table variable took the request from 34+ seconds down to less than 1.