Indexed Views And Data Modifications

Love/Hate

When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too.

But SQL Server is sort of smart about some modifications. Why it isn’t smart about all of them is beyond me. Let’s take a look at why, with StackOverflow.

Silly Demo

We’ll jump through the usual hoops to create an indexed view first.

Let’s look at where SQL is smart first. This section is lovingly called “inserts”. Our indexed view is filtered on people with a Reputation higher than 1, and whose account was created in the year of our deterministic date, 2016. When we insert single rows in the table, which is how many OLTP apps work, SQL is able to figure out which data is going to require updating the indexed view index.

The execution plans for these are what we’d hope for. Where rows don’t qualify for our indexed view filter, it doesn’t touch the indexed view.

100%

Unfortunately

This doesn’t seem to extend to updates and deletes. If we insert a single row that doesn’t qualify for our indexed view filter, then update it so it really doesn’t qualify, and then delete the rows, SQL turns into a big dumb dumb.

Oh, get out.

You did so much extra work.

Now, I know. That’s not really fair. We’re just updating and deleting based on the ID, and while it’s reasonable to think SQL might keep track of what’s in the indexed view, or that it might just check the indexed view and then discard the unnecessary operators, it doesn’t. In fact, even if we give the optimizer some extra help, it only kind of works.

Let’s be explicit about the fact that this row couldn’t ever possibly be in the indexed view.

Two out of three ain’t bad. The insert and the delete get it. The update is still being a big dummy. I’m not calling update plans easy to implement; I’ve read Conor’s chapter more than a few times. Updates are hard. In this case, though, roughly 72% of the plan cost is invested in the indexed view nodes of the plan.

Why, update? Why?

I think this is weird, too

It’s certainly not what I expected to happen when I first decided to test this out. To be fair, I didn’t invest much time in trying to find a workaround for the update plan. Frankly, you shouldn’t have to. I did try changing the WHERE clause to lower values, and direct equality comparisons, but nothing changed the plan.

Thanks for reading!

, , ,
Previous Post
No seriously, don’t use SQL_VARIANT for that
Next Post
sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use

5 Comments. Leave new

  • Good to know! How do you usually come across these finds?

    Reply
  • Hi Erik, great write up.

    We’ve come across a similar but opposite(?) issue where the inserts generate “super dumb” plans instead of updates being the issue when updating an indexed view. We’ve worked around this by doing a separate two statement insert-then-update (using similar forcing-redundant-logic-techniques) for rows/data shapes that convinces the compiler into a sensible and efficient plan for both. Our issue however isn’t totally the compilers fault, we have multi-column clustering keys and some other bad/complex statistics and data shapes that lead to parameter sniffing issues and cardinality-row-estimates being partly (or mostly!) to blame for confusing the query compiler.

    Does the issue perpetuate if you change the scenario to be purely equality and not equal operators instead of less/greater than operators? Perhaps the compiler isn’t able to reconcile 1 as being opposites?

    Reply
    • Sorry I should have read your disclaimer at the bottom about already having tried changing everything to direct equality comparisons, that’s partly what we used to help work around our insert/update multi-table-filtered-indexed-view issue was to match the filters and indexed keys of the view exactly to make sure the compiler knew we really meant it!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}