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

alter index [...] on [...] disable
alter index [...] on [...] disable

alter table [...] drop constraint [...]
alter table [...] drop constraint [...]

-- several such selects transfering around 10M rows each
select ...
into [...] -- that table already exists
from ....

update top (5000000) [...]
set [...] = up.update_id
from [...] t
inner join #to_update_part up on t.id = up.id

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 MEMOBJ_LOCKOWNERS.

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?

What's wrong?

  • Try adding the WITH (TABLOCKX) hint to the UPDATE statement. – Dan Guzman Nov 12 '16 at 13:25
  • I would suggest instead of going for 5000000 rows reduce it to 500000, updating so many rows in one go often creates issues. And does this whole update statement is really slow/has bad performance or you are just worried by memory consumption by locks ? – Shanky Nov 12 '16 at 14:23
  • @Shanky The problem is - after running this script DB engine is effectively broken, because is seems that memory is leaked somehow and will not be reclaimed. So, I can not run any complex query after that happens. – Worldexe Nov 12 '16 at 15:12
  • Does something like CHECKPOINT, followed by DBCC DROPCLEANBUFFERS not clear the memory? – Randolph West Nov 15 '16 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.