locked
Out of Memory exceptions with SQL Spatial RRS feed

  • Question

  • I'm running a process which uses BufferWithTolerance repeatedly to make surrounding shapes for our geographic data.

    After a while, I'm getting the out of memory error:
    .NET Framework execution was aborted by escalation policy because of out of memory.

    When I then run the same sql (from a SP) in a query window all is good and when I restart the SP it runs well (for a while).

    I have increased the "MemToLeave" parameter according to:
    http://support.microsoft.com/kb/969962

    It is running now with that change. Any other ideas? (I'd hope MS would actively work on this issue as it is a game-breaker.)
    Wednesday, May 27, 2009 3:22 PM

Answers

  • I have put a workaround onto the connect link. Also Microsoft replied. It appears they are working on this and have a hot fix for it and it will be in a release at some point but they are not sure when.

    My work around is just to use the Reduce method before doing Buffering to simplify the shape before buffering. Because my buffers do not need to be exact that works well for me and also improves performance a lot.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=463356
    • Marked as answer by HoosierSql Friday, June 19, 2009 2:19 PM
    Tuesday, June 16, 2009 10:15 PM

All replies

  • If the sp runs ok for a while but then eventually runs out of memory, it sounds rather like you have a memory leak somewhere...
    BufferWithTolerance() is memory-intensive, but so long as you are committing the results in small transactions you shouldn't have too much of a problem unless your shapes are huge (and your memory is tiny ;)

    Hard to suggest anything else without knowing how you're actually using the sp.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, May 27, 2009 3:32 PM
    Answerer
  • The SP loops through state, by state and makes a surrounding shape for all zip codes in the state (we have similar SPs for other shapes).

          INSERT INTO staging.geo (geo_name
                          ,geom
                          ,staging_geo_status_id
                          ,geo_source_id
                          ,create_date
                          ,create_user
                          ,modify_date
                          ,modify_user
                          ,geo_object_type_id
                          ,surrounds_geo_id
                          ,state_fips)
              SELECT 'Surrounding ' + g.geo_name
                     ,dbo.buffer_geo(g.geom, 1, 5)
                     ,3 -- Auto Approved
                     ,0 --MapInfo Loader
                     ,CURRENT_TIMESTAMP
                     ,SYSTEM_USER
                     ,CURRENT_TIMESTAMP
                     ,SYSTEM_USER
                     ,9 --surrounding zip
                     ,g.geo_id
                     ,g.state_fips
              FROM staging.geo g
              INNER JOIN staging.geo_state st ON st.staging_geo_id = g.staging_geo_id
              WHERE g.geo_object_type_id = 8 --zip
                    AND st.state_fips = @stateFips
                    AND NOT EXISTS (  SELECT 1
                                      FROM staging.geo sgi
                                      WHERE sgi.surrounds_geo_id = g.geo_id);

    buffer_geo returns a GEOGRAPHY which has been created by @a_geom.BufferWithTolerance(@v_buffer_distance,.2,1), where the distance is somewhere between the floor (first param) and ceiling (second param) in square miles.

    So it is commited upon every insert (autocommit) and like I said it runs successfully after I restart it (for 45-60 minutes, then another OOM error).

    It errored out again after 50-ish minutes. I'm thinking about putting in DBCC FREESYSTEMCACHE after each insert.
    • Edited by HoosierSql Wednesday, May 27, 2009 4:02 PM
    Wednesday, May 27, 2009 4:01 PM
  • This brings up something I have been wondering about.  I have noticed that various spatial operations are very resource intensive.  If there are many concurrent users performing spatial queries it would seem that this type of problem would pop up more frequently.

    Questions:

    1).  Any suggestions for things to monitor that might be indicative of resources running low?  performance counters, system views or diagnostics, 3rd party tools, etc?

    2). Using the Enterprise edition of SQL2008, apparently a resource governor has been included.  Has anyone tried using it related to either spatial queries or tasks such as building spatial indexes, loading data, etc?
    Wednesday, May 27, 2009 4:49 PM
  • I'm not sure if this helps, wimpy1. But i'm doing bucket loads of spatial stuff and i'm re-architecting our DB to handle caching more of this static data. By caching, i mean having tables with various pre-caluclated stuff and saving the results as geography. Downside is that the HD usage shoots right up as i'm now suddenly saving bucket loads of GEOGS (read: VARBINARY stuff). Upside is that i don't have to do STIntersects or STFilters (well, at least not as many as before).

    for example. I noticed you are playing around with state information. Imaging u have a table of POI's. To help speed things up, u might figure out which POI exists in which STATE. so when u need to do some spatial cals on the fly (eg. which POI's are within 2 KM's of me), u can suddenly filter all the results by state, which removes a bucket load of potential results the query would have orignially had to go through.

    Things like that.

    As for monitorng? I use the sql 2008 Activity monitor and the cpu usage and the hard disk usage (how much trashing of the HDD is going on).

    HTH.
    -Pure Krome-
    Friday, May 29, 2009 4:37 AM
  • Yep, definitely will want to take some "shortcuts" where they make sense.

    Regarding the monitoring, I'm hoping for something that can be automatically lurking in the background and send out notifications when certain thresholds are exceeded.

    The concept of the resource governor seems promising, but as it is a new feature I'm curious as to how it could be used for preventing any spatial task (super expensive query, creating a spatial index, etc) from killing the server performance for other user tasks and any "real world" feedback on how well it works (and any gotchas people may have encountered).

    Friday, May 29, 2009 3:20 PM
  • We have Lock Pages in Memory option set for the account that SQL runs under.

    I found the issue. It was actually a bug in my conversion logic that was giving the BufferWithTolerance function a very high value for the distance parameter and this apparently eventually led to the OOM issue. I was converting miles to meters, but I incorrectly used the logic to convert sq miles to sq meters which blew everything up.

    It appears to be running fine now that we have the correct conversion going and a more reasonable value for the distance parameter.

    Thanks!
    • Marked as answer by HoosierSql Friday, May 29, 2009 6:26 PM
    • Unmarked as answer by HoosierSql Tuesday, June 2, 2009 8:48 PM
    Friday, May 29, 2009 6:26 PM
  • I have a reproduceable out of memory error problem with SQL Server Spatial.

    Here is the code:
    --BEGIN CODE

    declare
    @points int = 1000
    declare @radius float = 1
    declare @granularity float = PI() * 2 / @points
    declare @bufferSize float = 1
    declare @circle as geometry

    set
    @circle = 'POINT (0 0)'
    declare @count float
    set
    @count=0
    while(@count < @points)

    BEGIN

     

    declare @x float = (@radius * cos(@granularity * @count))
    declare @y float = (@radius * sin(@granularity * @count))
    declare @newpoint geometry = 'POINT(' + CONVERT(varchar,@x) + ' ' + CONVERT(varchar,@y) + ')'

    set
    @circle = @circle.STUnion(@newpoint)
    set @count = @count + 1
    END

    set

     

    @circle = @circle.STConvexHull()
    print @circle.STIsValid()
    print @circle.STNumPoints()
    select @circle

    --select @circle.STBuffer(@bufferSize)
    --END CODE

    If you uncomment the last select, SQL Server chokes and starts using up memory like crazy.

    The code is quite simple. It makes a (rough) circle of 1001 points and then buffers it.

    Things of note:

    * The circle is considered a valid polygon
    * Buffer sizes less than 1 do not seem to cause a problem. 1 or greater do.
    * The behavior also occurs using the SqlTypes C# library

    Any way we can get this to Microsoft? If others can confirm, that would be a good way to get their attention.

    Thanks,
    Dave Felcan

    Monday, June 1, 2009 1:51 PM
  • I was able to reproduce on my SQL2008 installation.  While I did not get the out of memory exception, it took 42 seconds to run and watching page file usage history via Windows Task Manager on the sql server it increased by about 1Gb.  I am running SQL2008 SP1 x64 on Windows Server 2003.  This particular box only has 4Gb of ram.

    Looking at the query, I suspect is has something to do with the radius of the circle and the buffer amount being equal.  If I set the radius to 5 and leave buffer set to 1 it runs in under a second with no visible change shown in Windows Task Manager.

    Setting radius to 5 and buffer to 5 and running again, it took 27 seconds to complete and memory did not change. 


    You can submit feedback to Microsoft via their "Connect" website.

    http://connect.microsoft.com/SQLServer/

    After you register and sign in, select "Feedback"'

    They will want to know info about your hardware/OS used in your test.
    Monday, June 1, 2009 2:26 PM
  • I get the same behaviour as wimpy1 on my laptop - no error, but it takes a long time to get the correct results. And I also agree that, at least at first glance, the problem appears to manifest when the buffer size equals or exceeds the radius.
    To try this out, I tried an alternative method of obtaining a circle - this one has 1,024 points but, like yours, still has a radius of 1 and is centred on (0,0):
    DECLARE @circle2 geometry = geometry::Point(0,0,0);
    SET @circle2 = @circle2.STBuffer(0.25).STBuffer(0.25).STBuffer(0.25).STBuffer(0.25);
    SELECT @circle2.STBuffer(@bufferSize);
    You can play around with the values, but pretty consistently as @bufferSize approaches the radius of the circle (i.e. the sum of the individual buffer values), the query gets exponentially slower. I guess that this must be related to the implementation of how the buffer is calculated - you also get the same problem with BufferWithTolerance().

    Interesting spot - I can't find any known issues reported concerning this, so recommend you raise a connect issue to get it investigated.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, June 1, 2009 5:41 PM
    Answerer
  • Once someone has posted some feedback via Connect, can they post the link here please so a number of us can promote the issue, offically?
    -Pure Krome-
    Monday, June 1, 2009 11:59 PM
  • Yeah, I'm still getting the error also. It appears that now the problem just is not as bad.

    I'm still getting the error:
    .NET Framework execution was aborted by escalation policy because of out of memory.

    Now I just get it less often. I've also found that the more memory I have assigned to the sql server the more time it takes to get the error. If I have 10 GB assigned to the server it runs for 4 hours. When I have 3 GB assigned to the server it only runs for a little over an hour. This is run in batches of smaller transactions that are committed every couple of minutes.

    I've got to believe this is a memory leak bug with the BufferWithTolerance and STBuffer spatial udfs.
    Tuesday, June 2, 2009 8:52 PM
  • https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=463356

    Please comment away! Lets get Microsoft to address this!
    • Edited by HoosierSql Tuesday, June 9, 2009 2:10 PM
    Tuesday, June 2, 2009 9:11 PM
  • HoosierSql - this would be a good one to submit on Microsoft Connect.  Please submit it and then post the link here for others to "vote" on it if they wish.

    Hard to know if it is a "memory leak" or a .Net garbage collection problem.  Since the spatial stuff is really running within SQL as a CLR assembly, it will be subject to the .net memory management.  If I had more time I would dig into it using Reflector, but swamped already.

    FYI - some interesting reads on .net memory management are:

    "Identify And Prevent Memory Leaks In Managed Code"
    http://msdn.microsoft.com/en-us/magazine/cc163491.aspx

    "Garbage Collection: Automatic Memory Management in the Microsoft .NET Framework"
    http://msdn.microsoft.com/en-us/magazine/bb985010.aspx

    "Garbage Collection Part 2: Automatic Memory Management in the Microsoft .NET Framework"
    http://msdn.microsoft.com/en-us/magazine/bb985011.aspx


    Tuesday, June 2, 2009 9:25 PM
  • cheers HoosierSql :) i did my part :)
    -Pure Krome-
    Wednesday, June 3, 2009 2:39 AM
  • BUMP

    Please post to the below connect feedback for this issue:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=463356
    Monday, June 8, 2009 4:46 PM
  • I have put a workaround onto the connect link. Also Microsoft replied. It appears they are working on this and have a hot fix for it and it will be in a release at some point but they are not sure when.

    My work around is just to use the Reduce method before doing Buffering to simplify the shape before buffering. Because my buffers do not need to be exact that works well for me and also improves performance a lot.
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=463356
    • Marked as answer by HoosierSql Friday, June 19, 2009 2:19 PM
    Tuesday, June 16, 2009 10:15 PM