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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.t1 (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(1000)); GO CREATE TABLE dbo.t2 (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(1000)); GO CREATE OR ALTER VIEW dbo.vwBeetle WITH SCHEMABINDING AS SELECT t1.ID AS ID1, t2.ID AS ID2, COUNT_BIG(*) AS BIG_COUNT FROM dbo.t1 INNER JOIN dbo.t2 ON t1.ID = t2.ID GROUP BY t1.ID, t2.ID; GO CREATE UNIQUE CLUSTERED INDEX CLIX ON dbo.vwBeetle(ID1, ID2); GO |
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:
|
1 |
SELECT * FROM dbo.vwBeetle; |
Then, over on the primary, drop the index – but not the view:
|
1 |
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:
|
1 |
DBCC FREESYSTEMCACHE('ALL') |
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.
12 Comments. Leave new
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.
Samuel – I don’t think so, but a recent CU was related to problems running sp_WhoIsActive in a tight loop, though.
Wait, really? Which one?
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.
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?…
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.
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!
What version of SQL did this occur on?
For me, 2017 CU6 (current), and folks on Twitter have repro’d it on the rest.
Is it safe to assume that this is a non-issue on AGs that do not have Read-Only replicas?
Seems safe to me, but you know what they say about “assume.”