An idea to optimize this code
What is the data type of the @Mode parameter and the data type of the Mode column in local_bmsampleResults? Why don't these match? The most expensive statement in the session contains an index scan which may not need to be a scan except for the implicit conversion of the column to nvarchar(20). So the first thing I would do is to ensure those match. Next, this whole plan seems to have run in 0.003 seconds – how much faster do you expect it to be, especially since you are updating 7 non-clustered indexes (and that's only in the single statement I've already mentioned)?