I have run into very odd SQL Server memory consumption. The problem reproduces on standalone (without any production load) test server (24 cores, 64Gb RAM; SQL Server 2012 Enterprise version 11.0.5058.0 on Microsoft Windows NT 6.3 (9600)). Server itself is KVM virtualized with virtio drivers on top of Ubuntu 16 server.
Database is around 1.8T in size.
I need to update a lot of rows in that DB (around 300 million). To do that, I made a script and run it via SSMS, that looks like:
set nocount on go alter index [...] on [...] disable ... alter index [...] on [...] disable go alter table [...] drop constraint [...] ... alter table [...] drop constraint [...] go -- several such selects transfering around 10M rows each select ... into [...] -- that table already exists from .... go update top (5000000) [...] set [...] = up.update_id from [...] t inner join #to_update_part up on t.id = up.id go
At some point, script stops working.
Checking active commands shows that its locked with
RESOURCE_SEMAPHORE wait type.
sys.dm_os_memory_objects shows that 20+G of memory occupied by
MEMOBJ_LOCKBLOCKS and 10G+ by
sys.dm_tran_locks shows nothing suspicious.
I can not use
sys.dm_exec_query_resource_semaphore (there is no such view on my server - it looks like it was added in some SQLServer version above mine).
There are no other active transactions (this is clean server without any load). Stopping script and closing SSMS does not help (I can not proceed with script further, it waits the same lock). Dropping database and restoring it from backup does not help.
So, there is only one thing that frees that memory - server reboot.
As far as I understand, that memory is consumed by locks that are taken by update commands; but there are no active transactions. Why it did not get free?