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:
CREATE TABLE dbo.t1 (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(1000));
CREATE TABLE dbo.t2 (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(1000));
CREATE OR ALTER VIEW dbo.vwBeetle WITH SCHEMABINDING AS
SELECT t1.ID AS ID1, t2.ID AS ID2, COUNT_BIG(*) AS BIG_COUNT
INNER JOIN dbo.t2 ON t1.ID = t2.ID
GROUP BY t1.ID, t2.ID;
CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwBeetle(ID1, ID2);
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:
SELECT * FROM dbo.vwBeetle;
Then, over on the primary, drop the index – but not the view:
DROP INDEX CLIX ON dbo.vwBeetle;
On the secondary, run your query again, and – uh oh:
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:
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:
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.