SQL Server – App Domain Unloaded When Using GDAL/OGR to Insert Geometry Records

memoryspatialsql serversql-server-2012

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 inevitability, AppDomain will be unloaded.

Only setting the memory to 8GB will fix the problem, no more AppDomain issue.
However on production SQLServer, we can't get 8GB for our app, the server is much busier, and SQLServer ended up unloading our AppDomain.

Update 2
–script to reproduce the issue
–increase to 40M loop, usually failed at 27M features inserted, for 2GB RAM

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

USE [test_appDomainUnload]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[test_appDomainUnload](
[ogr_geometry] [geometry] NULL,
[id] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DECLARE @i bigint = 0

WHILE @i < 40000000
BEGIN
    SET @i = @i + 1
    BEGIN TRANSACTION
        /* do some work */
        INSERT INTO [dbo].[test_appDomainUnload] ([ogr_geometry]) 
        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())
    COMMIT
END

Update 4
Attaching the data (FileGDB), ogr2ogr.exe binaries and ogr.bat to execute it.
Edit ogr.bat to set your Database params, and run it.

https://drive.google.com/open?id=0By7mVmnZ1C1oT0lWVTJaZmlabTA

Update 5
To be clear, ogr2ogr.exe is located at:

we use v1.11, http://github.com/OSGeo/gdal/blob/1.11/gdal/apps/ogr2ogr.cpp,

trunk at http://github.com/OSGeo/gdal/blob/trunk/gdal/apps/ogr2ogr_bin.cpp

runs on another pc, separate to SQLServer machine.

Update 6
Exact Error Message:

time+0: AppDomain 9 (master.sys[runtime].8) is marked for unload due to memory pressure.
time+1: AppDomain 9 (master.sys[runtime].8) unloaded.
time+2: AppDomain 10 (master.sys[runtime].9) created.

It was already AppDomain 9, the previous 8 AppDomains (all master.sys[runtime]) have been unloaded and recreated.

Best Answer

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 < 40000000)
BEGIN
    SET @i = @i + 1;

    IF(@i % 100 = 0) -- Print message every 100 iterations
    BEGIN
        RAISERROR('%d -- %d', 10, 1, @i, @@TRANCOUNT) WITH NOWAIT;
    END;

    INSERT INTO [dbo].[test_appDomainUnload] ([ogr_geometry]) 
    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());

    IF(@i % 200 = 0) -- COMMIT every 200 iterations
    BEGIN
        COMMIT TRAN;
        BEGIN TRAN;
    END;
END;

IF (@@TRANCOUNT > 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 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 (<dbid>)

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 (<dbid>) a few times and see if the total memory consumed ever reaches what you have been seeing.

UPDATE 2

I did confirm that ad hoc (i.e. non-parameterized) SQL is being used, at least in v1.11, starting on Line 1041:

gdal/gdal/ogr/ogrsf_frmts/mssqlspatial/ogrmssqlspatialtablelayer.cpp (branch 1.11)

And it appears that support for doing bulk uploads was added in v2.0. So, you probably need to upgrade to the latest version (currently 2.1).

UPDATE 3

It occurred to me that if the issue deals with Plan Cache Bloat, then the test code (even my adaptation of it above), does not apply since it reuses the same INSERT statement each time and thus does not increase the number of plans in the cache. With this in mind, I further adapted the test code to use Dynamic SQL to alter the INSERT statement each time:

-- SELECT COUNT(*) FROM dbo.test_appDomainUnload;
-- TRUNCATE TABLE dbo.test_appDomainUnload;
-- CHECKPOINT;

SET ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT ON;

DECLARE @i INT = 0,
        @SQL NVARCHAR(MAX);

BEGIN TRAN;
WHILE (@i < 40000000)
BEGIN
    SET @i = @i + 1;

    IF(@i % 100 = 0) -- Print message every 100 iterations
    BEGIN
        RAISERROR('%d -- %d', 10, 1, @i, @@TRANCOUNT) WITH NOWAIT;
    END;

    SET @SQL = N'
    INSERT INTO [dbo].[test_appDomainUnload] ([ogr_geometry]) 
    VALUES (geometry::STGeomFromText(''MULTIPOLYGON (((503754.' +
            CONVERT(NVARCHAR(15), @i) + N' 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.' +
            CONVERT(NVARCHAR(15), @i) + N' 6952046.6778)))'',
           28356).MakeValid());
         ';
    EXEC(@SQL);

    IF(@i % 200 = 0) -- COMMIT every 200 iterations
    BEGIN
        COMMIT TRAN;
        BEGIN TRAN;
    END;
END;

IF (@@TRANCOUNT > 0) -- run manually if you cancel the query
BEGIN
    COMMIT TRAN;
END;

Watch the numbers grow via:

SELECT name, pages_in_use_kb , pages_kb
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP';

SELECT COUNT(*) AS [TotalNumberOfCachedPlans]
FROM sys.dm_exec_cached_plans;

I still was not able to force the AppDomain in master to unload, but definitely getting closer.

FINAL UPDATE

O.P. reported back that the suggestion in UPDATE 2 worked:

Rebuilding ogr2ogr.exe with BCP support and utilizing BCP to import into SQLServer got rid of this AppDomain unloaded issue, SQLServer Max Memory settings can even be lowered down to 1GB and everything is running OK.

Related Solutions

SQL Server 2008 – Max Memory Not Limiting RAM Usage

SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit.

Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start.

Mysql – Stress Test to Saturate Memory for MySQL InnoDB

From Windows Platform Limitations in the MySQL 5.5 Reference Manual:

On Windows 32-bit platforms, it is not possible by default to use more than 2GB of RAM within a single process, including MySQL.

What's to investigate? We already know what happens when you max the memory: Really Bad Things™.

If there's a remote chance that your setup will periodically require this much memory, 32-bit Windows is the wrong platform.

Almost universally, the biggest memory consumer is of course the InnoDB Buffer Pool, but there's no need for any queries to test this... you don't need any activity at all, or any tables, because the entire amount of memory declared for innodb_buffer_pool_size is allocated immediately when the server starts. The buffer pool never grows, never shrinks, never changes, ever. The number of free pages changes, but those are not "free" from the operating system's perspective -- they're still just as allocated, merely marked as containing nothing of interest within InnoDB.

If the operating system refuses to allocate the amount of memory provisioned for the buffer pool, MySQL will simply refuse to start.

This is illustrated here, where the OP mistakenly believed that the server was crashing and restarting "because" memory couldn't be allocated for the buffer pool, but was in fact crashing for a Linux-specific reason but then refusing to restart because the system would not allocate the total amount of memory required for the pool, due to overuse of available memory by something else... but this allocate-all-at-startup behavior for the InnoDB buffer pool is not platform-specific.

So, you should be able to set this value near the max and then find that taking the server process over the edge should not require very much additional effort at all. But I'm still not sure what the point is.

As you realize, MySQL uses memory for a variety of different purposes, several of which are dynamically-sized, definable on a per-connection basis, and allocated on demand, which makes it virtually impossible to provision a server based on limiting memory usage to some worst-case scenario absolute value, yet expecting that server to be able to handle its typical load efficiently.

The simplest illustration of this is the fact that you can obviously reduce the theoretical maximum memory utilization of a given instance by restricting the maximum number of simultaneous client connections... but any given application needs a certain number of available connections to perform efficiently, and if that number is below your target value, then you're not really solving anything -- it just feels like you have.

I say, either your server has enough memory for the workload, or it doesn't. If it doesn't, then attempting to "tune" your way out of potential trouble is unlikely to offer much in the way of solutions.

Some ideas on how to easily generate demand for more memory...

SELECT * FROM large_table ORDER BY non_indexed_column;

SELECT * FROM large_table WHERE non_indexed_column = some_value;

SELECT * FROM large_table WHERE some_column LIKE '%a_freqent_match%';

Queries like these could trigger the allocation of a sort buffer, a read buffer and/or a random read buffer, which should make a new request to the OS for the memory that buffer requires.

Simple solution:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`eat_memory_until_server_crashes` $$
CREATE PROCEDURE `test`.`eat_memory_until_server_crashes`()
BEGIN

-- this procedure is intended to eat as much memory as it can
-- it creates a series of consecutively-numbered session variables as large
-- as your configuration will allow them to be.

-- do not run this unless you intend to crash your server

-- also, do not run from a gui tool -- use the mysql command line client:

-- mysql> CALL test.eat_memory_until_server_crashes;

-- if you kill the query or thread before the server crashes, 
-- the memory consumed will be returned to the OS

  DECLARE counter INT DEFAULT 0;
  LOOP
    SET counter = counter + 1;
    SET @qry = CONCAT('SET @crash_me_',counter,' := REPEAT(\'a\', @@max_allowed_packet)');
    SELECT counter, @qry;
    PREPARE hack FROM @qry;
    EXECUTE hack;
    DEALLOCATE PREPARE hack;
    -- adjust timing or remove this entirely depending on how quickly you want this to happen
    DO SLEEP(0.1);
  END LOOP;

END $$

DELIMITER ;

Inspiration for this: Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12194). OReilly Media - A. Kindle Edition.

Best Answer

Related Solutions

SQL Server 2008 – Max Memory Not Limiting RAM Usage

Mysql – Stress Test to Saturate Memory for MySQL InnoDB

Related Question