Here’s a fun one I came across this week, we had what was supposed to be a relatively simple update statement taking forever to execute. The update was to an application database on another server, and the table had about 300k rows in it. We had two very similar update statements and only one of them was causing us any grief.
As mentioned, we were updating an application database, so used a cursor to lock the rows 1 at a time.
The original execution plan looked as follows:
So you can see the query is doing a full table scan of the remote table (the table we want to update).
Why is it doing this? Well after much head scratching we finally identified the difference between our working query and this troublesome query. Our @OrderOutcome parameter is of length 50, where as the data being entered into it is limited to 40 characters. The fix, update the @OrderOutcome parameter to 40 characters…
The resulting execution plan, now looks like this:
I can’t explain why this happens, but it looks to have been a problem ever since SQL 2005, and when you have 2,000 updates to do, doing a table scan for each of those can be quite tedious, but now you know. Make your parameters the same length as the longest data going into it.