I found what seems to be a bug in Microsoft SQL Server (v13.0.1601). Perhaps I am mistaken but I haven't seen reported anywhere else online so I will describe it below.
One section of our code uses spatial indexing. We have a table called 'SpritePositions' which contains Polygons and a 'Sprite' table, each entry of which links to a number of SpritePositions. Each Sprite has a polygon which is populated using the geometry::UnionAggregate call, combining each of the polygons of the appropriate SpritePositions.
Rarely, a Sprite may contain a large number of SpritePositions and the UnionAggregate call takes a little while to complete. Interestingly, this time is not linearly proportionate to the number of SpritePosition Polygons.
Some experiments indicate that performing a UnionAggregate takes around 4 seconds for 500 polygons, around 20 seconds for 1000 Polygons. For 10000 polygons SQL Server does this:
Looks like a memory leak in SqlServer. It never returns - ram usage keeps growing forever and CPU usage sits at one full core. No further sql calls return and the only solution is to kill the Sql server process.
So my question - Has anyone else encountered this? Is there something I'm missing?