Availability Groups Bug with Indexed Views

The short story: if you drop an index on an indexed view, queries on the Always On Availability Groups replicas that point to that indexed view will cause memory dumps.

The long story: to reproduce – AND DO NOT DO THIS IN PRODUCTION – on the primary, create a couple of tables, an indexed view, and hold my beer:

Over on the secondary, run a query against that view, and look at the execution plan, making sure it’s actually using the index on the view:

Successful

Then, over on the primary, drop the index – but not the view:

On the secondary, run your query again, and – uh oh:

The Kill State

What happened was that the secondary tried to use an execution plan that pointed to the index on the indexed view – but the index no longer existed.

The secondary’s error log shows a memory dump, always a good time:

Mama always said you were exceptional

And I know what you’re thinking – we just need to recompile the query to build a new execution plan that doesn’t include the index on the view:

Sad Trombone (which happens to be the official state song of the Kill State)

And no, sp_recompile and DBCC FREEPROCCACHE don’t work either. Even getting an estimated execution plan for anything related to the view causes a memory dump.

The only way to stop the continuous memory dumps is to free the system caches:

Microsoft is now aware of it, and working on a fix for a future cumulative update.

In Azure SQL DB, the problem manifests itself differently: instead of repeated memory dumps, the secondary replica restarts itself after the first query fails. Subsequent queries don’t because the server restart “fixed” the system cache issue. At first that sounds awesome – until you realize that the secondary replica can also be a primary replica for other databases that happen to be running on it.

I wanna give a big shout out to the nice folks at iQmetrix, especially Chris B. and Patrick H. They got SQL ConstantCare® alerts about a flurry of memory dumps on one of their replicas, so they opened a support case, kept us in the loop as the case progressed, and let us share the results with you to hopefully prevent more heartbreak until the patch comes out.

Previous Post
Parameter Fluid Optimization
Next Post
When Query Plans Lie Part 1

10 Comments. Leave new

  • Samuel Jones
    May 8, 2018 12:37 pm

    I’ve occasionally gotten a similar response from running sp_WhoIsActive on my secondary – could it be related? Honestly, once I saw it was just my query I’ve just blown it off as weird.

    Reply
    • Samuel – I don’t think so, but a recent CU was related to problems running sp_WhoIsActive in a tight loop, though.

      Reply
      • Alex Friedman
        May 15, 2018 9:09 am

        Wait, really? Which one?

        Reply
        • Alex – hit https://sqlserverupdates.com, and click on the Other SQL Server 2017 Updates list. From there, you can read the KBs for each cumulative update. Look for the word “monitoring stored procedure” – they didn’t wanna name it, hahaha.

          Reply
          • Alex Friedman
            May 15, 2018 10:48 am

            Oooh nice! I see it’s in 2016 too under SP1 CU8. I’m actually running whoisactive v11.17, so it’s not using the live query stats DMV — didn’t even know he implemented that, very cool. Will have to upgrade after patching SQL Server. Thanks!

  • It says in the above article that the ONLY way to resolve this would be to flush the system cache, I just need to know for my own sanity, would recreating the dropped index also resolve this?…

    Reply
    • Yep, as does restarting all of your replicas. Those aren’t really solutions though – you dropped the index because it was causing problems to begin with, like unacceptable performance on inserts/updates/deletes.

      Reply
      • Ah of course, you didn’t mention the reason for dropping the index in the article, that’s why i had to just check to be sure, cool thanks for putting my mind at ease here Brent!

        Reply
  • What version of SQL did this occur on?

    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":""}