I have a stored procedure that joins three large tables together (about 20 million records each) and loads records into a temp table. Data in the temp table is then merged into an existing table of about 60 million records.
The server went offline with an error message of:
AppDomain 2 (SSISDB.dbo.[runtime].1] is marked for unload due to memory pressure.
Once I got the server back online, I restarted SQL services to clear out any processes that may have been lingering. Kicked off the job again and it completed without issue.
I’m running SQL Server 2019 with 128GB of RAM. Maximum server memory is 117964MB on a 64-bit virtual server. Someone told me in Task Manager the memory usage was at 94% and that could be the issue. But doesn’t SQL take all of the memory available and hold on to it? So that seems like it’s operating as intended.
sp_WhoIsActive
revealed some queries with a status of 'Suspended' and 'Awaiting_Command', but I don’t believe those had much impact. 128GB of ram seems adequate, but I guess it’s relative to the job it’s being asked to do. Any idea how to troubleshoot or prevent from happening again?
The data drive for the server is about 1.6TB. The two larger databases in the join are 10GB with 19 million rows and 13GB with 20 million rows. Those go into a temp table and then MERGE
into a 26GB table with 53 million rows.
The requested memory grant is 45GB and the actual is 30GB. The job ran outside of regular business hours so there should have been no competing queries, but I can't 100% confirm that someone wasn't working late.
I did notice that it is having to do a CONVERT_IMPLICIT
as well. Does that have a significant impact on required memory?
Link to the query plan: https://www.brentozar.com/pastetheplan/?id=SyXaty7xK