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

I did not have the time to run your script and wait for SQL Server suffocate from lack of memory.

I like to point out, though, that the best place to report a bug which you want Microsoft to know about is here: https://feedback.azure.com/forums/908035-sql-server

If this is a blocking issue for you and you need a fix, you should open a support case.

Answer

Greetings from the product group.
I replied at the Azure Feedback item, but also wanted to follow up here for better visibility.
We were able to repro the issue and the fix is on its way.
You can track further progress at the Azure Feedback.

Answer

observe how memory allocation grows and never drops down

That's by design and the normal behavior of SQL Server to allocate as much memory as it needs and can get and hold it as long as possible.
SQL Server releases memory only on OS pressure, so far this not a memory leakage.

See Memory Management Architecture Guide

Answer

Hi

MS support is trying to gather some details from me and I found it very difficult how they try to achive that due to time zone differences and quality of communication. I'm still at their support first sieve level so it will take days or months before this will be confirmed or rejected probably.
I think confirmation from community should also help to prioritize my request, so I appreciate that you checked my script and confirm that it needs to be looked further :)

Regards

Answer

I ran that repo in 5 sessions for 24 hours with no noticable increase in memory utilization reported by SQL Server or Windows.

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) 
 Nov  2 2020 18:35:09 
 Copyright (C) 2019 Microsoft Corporation
 Standard Edition (64-bit) on Windows 10 Pro 10.0  (Build 19042: ) (Hypervisor)

Capturing a memory snapshot every minute

set nocount on
while 1=1
begin
    insert into allocation_history
    select getdate() ts, coalesce(type,'Total') type, sum(virtual_memory_committed_kb + awe_allocated_kb + shared_memory_committed_kb) / 1024. MemoryMB 
    from sys.dm_os_memory_clerks
    group by type
    with rollup
    having sum(virtual_memory_committed_kb + awe_allocated_kb + shared_memory_committed_kb) / 1024. > 0
    order by MemoryMB desc

    waitfor delay '00:01:00'
end

And this

select *, MemoryMB - lag(MemoryMb) over (order by ts) Delta
from allocation_history
where type = 'Total'
order by ts desc, memorymb desc

outputs

ts                      type                                                         MemoryMB                                Delta
----------------------- ------------------------------------------------------------ --------------------------------------- ---------------------------------------
2021-02-28 10:24:50.457 Total                                                        689.824218                              0.000000
2021-02-28 10:23:50.443 Total                                                        689.824218                              0.000000
2021-02-28 10:22:50.430 Total                                                        689.824218                              0.000000
2021-02-28 10:21:50.420 Total                                                        689.824218                              0.000000
2021-02-28 10:20:50.407 Total                                                        689.824218                              0.000000
2021-02-28 10:19:50.397 Total                                                        689.824218                              0.000000
2021-02-28 10:18:50.383 Total                                                        689.824218                              0.000000
2021-02-28 10:17:50.373 Total                                                        689.824218                              0.000000
 . . .
2021-02-27 10:21:31.490 Total                                                        683.648437                              0.000000
2021-02-27 10:20:31.477 Total                                                        683.648437                              0.000000
2021-02-27 10:19:31.463 Total                                                        683.648437                              0.000000
2021-02-27 10:18:31.450 Total                                                        683.648437                              0.000000
2021-02-27 10:17:31.440 Total                                                        683.648437                              0.000000
2021-02-27 10:16:31.427 Total                                                        683.648437                              0.000000
2021-02-27 10:15:31.413 Total                                                        683.648437                              0.000000
2021-02-27 10:14:31.400 Total                                                        683.648437                              0.000000
2021-02-27 10:13:31.387 Total                                                        683.648437                              0.000000
2021-02-27 10:12:31.373 Total                                                        683.648437                              0.000000
2021-02-27 10:11:31.360 Total                                                        683.648437                              0.000000
2021-02-27 10:10:23.150 Total                                                        683.648437                              NULL

(1455 rows affected)

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

8 answers