App Domain unloaded when using GDAL/OGR ogr2ogr to insert many Geometry records

-
SQL Server 2012 x64, Total memory allocated 2GB, set to low value to enable us to reproduce "AppDomain unloaded" issues much quicker

-
Windows Server 2012, Total Memory on Server 16GB

(in production, we have 64GB total memory, which SQL Server can take all of it if needed, this AppDomain unloaded issue still occurs)

SQL Server keeps unloading AppDomain once the allocated 2GB memory is full, which make sense.

-
what does not make sense is that, why SQL Server does not 're-use' the existing memory (or why does it hang on to the existing AppDomain, not reducing the memory used for existing AppDomain) that it has been using?

-
or is it simply the way SQLServer works, by unloading AppDomain to reclaim memory ?

-
the insert query uses the function geometry::STGeomFromText,

-
there are about 1,000,000 inserts,

-
which are committed in a transaction of 200 inserts each,
and looks like:

INSERT INTO [dbo].[gdb2] ([ogr_geometry],
[seg_num], [par_num], [segpar], [par_ind], [prc], [parish], [county], [lac], [shire_name], [feat_name], [loc], [locality], [parcel_typ], [cover_typ], [acc_code], [ca_area_sqm], [shape_length], [shape_area], [globalid])
VALUES (geometry::STGeomFromText('MULTIPOLYGON (((503754.43209999986 6952046.6778,503770.5603 6952043.9964,503790.83380000014 6952040.6264,503810.62700000033 6952037.3364,503824.2081000004 6952035.0799,503825.46609999985 6952014.4771,503805.18599999975 6952017.8472,503786.3114 6952020.9851,503766.46679999959 6952024.2841,503746.6699000001 6952027.5757,503754.43209999986 6952046.6778)))',28356).MakeValid(),
26332, 84, 26332084, 81, 6000, 'OBSOLETE', 'OBSOLETE', 1000, 'CITY', 'AVENUE', 2449, 'SALISBURY', 'R', 'B', 31, 1516.137, 191.870950878815, 1514.92744496079, '{6A0865B9-3D4A-4395-BC78-B2E16CF4E95B}')


Update

Thanks @cody.
Yes 2GB is not enough, but on production, we have 64GB and it is still not enough, that SQLServer is also much busier though.

Increasing the memory to 4GB only postpone the inevitab

Providing the exact message from the Log\ERRORLOG file would be quite helpful since there are both multiple AppDomains and multiple reasons for AppDomains to get unloaded.

Regarding the various AppDomains, there are AppDomains created for built-in CLR-based functionality, such as one that shows up in master when certain functions (e.g. FORMAT) and/or certain types (e.g. GEOMETRY) are used. And there are AppDomains created in user databases when custom SQLCLR code, located in Assemblies that you created, is accessed.

The unloading of "system" AppDomains should only be, at worst, a performance issue if it is happening rather frequently. If it is happening only occasionally then it is really just an annoyance.

The unloading of "user" AppDomains has the potential to cause errors and/or unpredictable behavior if the custom SQLCLR code is using shared memory via static class variables.

Regarding the various reasons for AppDomains to get unloaded, it could be due to executing something like:

DBCC FREESYSTEMCACHE('ALL');


which results in a message of:


AppDomain xx (master.sys[runtime].yy) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.

A more common reason for AppDomains to get unloaded, and more likely the subject of this Question, is due to "memory pressure". Memory Pressure is when the amount of available physical memory shrinks and is used by applications as an indicator to more aggressively free up memory. SQL Server frees up memory by clearing out items from the plan cache as well as unloading AppDomains.

The causes of memory pressure are not necessarily the use of CLR-based functionality within SQL Server: anything running on the system, whether internal to SQL Server or external and unrelated to SQL Server, can use up memory and cause memory pressure. For more info on "memory pressure", please see the MSDN article: Plan Cache Internals.

From the info provided in Update 3 in the Question:


this script below does NOT reproduce the issue, --only ogr2ogr.exe to upload WellKnownText/WKT to SQLServer does it.

it would seem that the ogr2ogr.exe program somehow grabs a lot of memory and does not release it quickly enough (or perhaps at all?). This could be due to several reasons, including bad programming practices, but it is difficult to narrow down without being able to see the source code. And even if we can see the source code, there is not much that can be done about it unless this were Open Source and we could make changes and recompile it. Or perhaps this issue can be reported to the developer(s) of ogr2ogr.exe who might be able to fix it.

P.S. The test code in the Question is not exactly representative of what is described in the Question as the operation being performed:


there are about 1,000,000 inserts, which are committed in a transaction of 200 inserts each

since there is only a single statement per each Transaction in the example code (and hence no real reason to even have an explicit Transaction).

In order to more closely test the described behavior, use the following adaptation of the posted test code:

SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;

DECLARE @i INT = 0;

BEGIN TRAN;
WHILE (@i 0) -- run manually if you cancel the query
BEGIN
COMMIT TRAN;
END;


NOTE: If you cancel the query, you should manually execute the final IF block to ensure that no Transaction is left open.

On my test system (which has barely any free memory as I also use it for SSMS, Visual Studio, etc) the survived_memory_kb field of sys.dm_clr_appdomains never went above 1. (and I would not use the total_allocated_memory_kb field as a reliable metric since it is a total of all memory allocations made, not a current allocation value.)

UPDATE

New info provided indicates that ogr2ogr.exe is running on a different machine than SQL Server, and is connecting via TCP. This suggests that the issue could be related to connections (is Connection Pooling being used? are the Transactions being handled across a single Connection or a new Connection each time? if new Connections, is the app release the Connection object so that the Connection can be closed?).

OR the memory consumption could be related to how the INSERT queries are being submitted: a parameterized query would have a single plan in the cache and it would get reused per each INSERT, but if the queries are ad hoc (i.e. the new value is being concatenated in per each INSERT) then that could cause Plan Cache Bloat. The following resources can help investigate:

• The Plan Cache

• Troubleshooting Plan Cache Issues

The first link shows a DBCC command to flush the plan cache in a single DB (a bit safer than clearing out the cache for the entire instance):

DBCC FLUSHPROCINDB ()


But this DBCC command does not seem to be otherwise documented.

An easy-enough test would be to run ogr2ogr.exe on the test system, and while it is running, execute `DBCC FLUSHPROCINDB ()

StackExchange Database Administrators Q#137902, answer score: 3