3

  • 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.

  1. 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?

  2. 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 ogr binaries1

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.

Improve this question
0

2 Answers 2

Reset to default
3

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.

Improve this answer
0
1

I met a similar issue recently when inserting huge amount of data using ogr2ogr from shapefile to SQL Sever. The error message looks like:

ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][SQL Server]The app domain with specified version id (46) was unloaded due to memory pressure and could not be found.

And the solution is simply to add -skipfailures to your ogr2ogr command line. This may be due to memory pressure.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.