SQL Server 2016/2017/2019 reproducible memory leak scenario with spatial index search - is there a patch?

Question

Hi,

I found out that searching geography points with spatial index may lead to irrecoverable memory leak scenario, that is after some long running time only SQL Server resatrt helps and performance of SQL Server is severely degraded. Is there a patch for it or workaround? How it could go unnoticed?

Here below I put a reproduction script, it may be overwhelming but I'm just not sure which part is causing the leak actually. After several hours the script may lead to performance issues so please don;t run it on production servers!

==============
SQL SCRIPT STARTS HERE ====================
--1. setup DB and tables
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE LeakTest
GO
USE [LeakTest]
GO

CREATE TABLE [dbo].Location
GO

--when there is no index then memory does not leak
CREATE SPATIAL INDEX [idx_spatial_Location]
ON [dbo].Location USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 4)
GO

--1.1 setup - populate table with locations
truncate table [dbo].[Location]

declare @x float=10
declare @y float=59
DECLARE @START float = 0
DECLARE @Eslam Nader float = 10
DECLARE @increment float = 0.01
;WITH RANGES
AS
(
SELECT @START [From],
@START + @increment [To]
UNION ALL
SELECT [To],
[To] + @increment
FROM [RANGES]
WHERE [To] < @Eslam Nader
)

INSERT INTO [dbo].[Location] ([Location])
SELECT geography::STGeomFromText(CONCAT('Point(', @x + Rx.[From], ' ', @y + Ry.[From],')'),4326)-- @x + Rx.[From], @y + Ry.[From]
FROM [RANGES] Rx, [RANGES] Ry
OPTION (maxrecursion 10000)
GO

--2.0 actual test, can be executed in different session(s) as well
set nocount on
declare @p3 sys.geography
set @p3=geography::STGeomFromText('POINT (10.0554 59.0004)',4326)
DECLARE @VID INT

while 1=1
BEGIN
SELECT TOP(1) @VID = [c].[Id] FROM [Location] AS [c] WHERE [c].[Location].STDistance(@p3) <= 10 ORDER BY [c].[Location].STDistance(@p3)
END

--2.1 execute in different session to observe how memory allocation grows and never drops down, until sql server runs out of memory limits
/*
select top 10 pages_in_bytes / (1024.0*1024.0) as allocatedMB, waiting_tasks_count, * from sys.dm_os_memory_objects
where page_allocator_address=(select top 1 page_allocator_address from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SOSNODE' order by pages_kb desc)
order by pages_in_bytes desc
*/

--3.0 cleaning - execute when done
/*
USE [master]
GO
DROP DATABASE [LeakTest]
GO
*/

==============
SQL SCRIPT ENDS HERE =====================

Answer 1

Hi,

I've got first feedback from MS support engineer, he confirmed that he is observing memory allocation growing when using spatial index + order by clause.
He suggested not to use spatial index or order by clause, because then memory allocation is not growing then (and I confirm but it was stated in problem description).
Also between words he denied that there is memory leak, just I'm not using the product in correct way...

I'm totally disappointed with his advice, I guess he works for other DB server vendor and other memory leaks fixed in various CUs could be also "fixed" in that silly way ;)

Regards

Answer 2

This is great news, thank you and I'm waiting for updates/patches :)

Answer 3

Hi everyone,

We are also experiencing performance issues on a production SQL Server 2014 (latest SP/CU installed) hosting databases using spatial indexes. After 4 or 5 weeks of execution, the all SQL Server performances are going down and require a restart to solve the problem. At that moment, the stolen memory is about 75%. And MEMORYCLERK_SOSNODE seems to be the culprit.

Have you received any more information from Microsoft since the last message posted on 20 March 2021 ?

Thanks,

Patrick