Query turned into a CPU gobbling monster
The first query is so slow because it will do a full table scan on thing.table for every row in that table for which DAY(SnapshotDate) &lt;&gt; 1. So if you have 100k rows in the table in the worst case you'll do 100k scans which means reading through 10 billion rows. If the table is small enough it'll stay in memory so your parallel query will appear to burn through CPU.

You can tell by looking at the query plan carefully. The scan is on the inner side of a nested loop join. If that's not your cup of tea you can try live query statistics to see the query as it executes. That way you can get some of the information from the actual plan without needing the query to finish. There's no way to save off old actual plans without setting up extended events.

The second query is faster because the query optimizer is freer to rearrange the elements of the query due to the lack of the CASE expression. Instead of calculating the subquery for MAX(SnapshotDate) once per row the calculation is done once per query.

You'll definitely want to fix this query in some way or the execution time will continue to grow quadratically with the number of rows in the table. One workaround would be to add an index to the SnapshotDate column. The subquery will still execute once for each row but getting the maximum value will be a very cheap operation. A better way is to save off the value of the subquery to a local variable and to use that in your UPDATE query. Unless you have to worry about concurrency that shouldn't be an issue.

You can also stick to the fix that you found if you want. One suggestion that can help in some cases (depending on the table structure) would be to add a where clause to your first UPDATE:

UPDATE thing.table
SET YYYYMM = NULL
WHERE YYYYMM IS NOT NULL;