I'm working with the Geography datatype in SQL2008 and am noticing some worrying memory characteristics.
I have about 1 million locations registered as points (latitude and longitude)
My developers are passing in a point into a stored proc which takes that point and applies the STBuffer method the retrieves a count of the number of locations that STIntersect the resulting circle.
STBuffer makes a ploygon from the point made up of 80 points.
If I throw calls to that stored proc at the rate of 3 per second the system works fine but if I up the rate to 5 or more then the duration of the queries get larger and larger until the system becomes unworkable. If I wait a few seconds and start calling the proc again then the duration drops back down to acceptable levels.
It looks as if garbage collection in SQL cannot keep up with the creation of objects.
I did suggest that the developers do the STBuffer in their code but the problem is that the geography types depend on two DLLs. One has no trouble being referenced by a .NET app but it makes references to the 2nd DLL for certain functions and .NET doesn't like referencing it.
I'm not sure how to proceed. Has anyone had any experience with this?