Jared Poche experiences Halloween problem protection:
Simple enough so far. The scan is against a memory optimized table variable, and the filter to the left our our seeks and scans check for a change to our value. Nothing left but to update the index and…
Wait, what’s all this? We have a Split operator after our Clustered Index Update. SQL Server does sometime turn an UPDATE statement into effectively a DELETE and INSERT if the row needs to move, but this seems a bit much. We have a total of 4 index update/delete operators now, and they aren’t cheap.
My very simple addition to the WHERE clause actually caused a small increase in duration, and a big jump in CPU. So what’s going on?
Read on to see the cause and what Jared was able to do about it.