Transcript

A quick summary of what we saw with all these execution plans.

1. For user database tables…

What we’re seeing is that sometimes we get a wide plan, and sometimes we get a narrow plan, depending on what parameter value we pass in when we compile the query.

For this “permanent” table, it performs fast with the wide plan or with the narrow plan.

2. But by comparison for our temp table…

When we’re updating a lot of rows, when we optimize the plan for an update of 800,000 out of a million rows, we’re getting a wide plan that is much slower than a very similar looking wide plan against the “permanent” table.

The wide plan for this temp table is doing way more IO, and the IO is on both the operator that finds the rows and the operator that updates the rows. That’s very different than it is in the other plan.

For the temp table, if we compile the procedure for a small amount of rows, sometimes we do get a narrow plan. That plan doesn’t have that extra IO, and its fast which is very very cool, but we’re only going to get that plan when we compile the procedure for a few amount of rows.

3. If we use a table variable…

We get that narrow plan, and it’s fast as long as we don’t use a recompile hint when we’re doing the update.

4. If our update statement against the table variable has a recompile hint in it…

Even if we’re just doing one row, we get a wide plan and we get this inflated IO against it.

Pulling some of this information together

What have we learned?

Wide update plans aren’t *always* slow.

It’s not that narrow plan is always fast and wide plan is always slow.

We got a wide plan when updating that user database table and it was fast. But the wide update plan that we’ve gotten for the temp table and the wide update plan that we get for the table variable when we use the recompile hint– that wide update plan has these extra IO characteristics on it, and it is much slower.

We’ve got an issue with *some* wide plans.

Next up, we’re going to dig into this a little more and check out: okay does this issue happen if I’m using ad hoc SQL? Or is this specific to stored procedures?