Clock hands churning and high stolen memory

Question

Post reply

Clock hands churning and high stolen memory

Ian (SSC)

SSC-Addicted

Points: 407
More actions
March 17, 2017 at 10:02 am

#335748

Hi,
I've been observing some odd behaviour on one of our servers and was wondering if anyone could shed any light on this:
Server Details:
Virtualised
SQL2014 – SP1 (12.0.4100.1)
CPU Count: 4 (Hyperthreaded - 2 Physical cores)
Server Mem: 16GB
SQL Mem: 12GB
The server has a very light load, to the point of barely being used by our application.
Given the fact that there is a very light load I continually see the CPU (via the ring buffer) at about 23%. Further investigation has shown:
Target mem: 12,582,912 kb
Total mem: 12,383,504 kb
Stolen mem: 8,801,248 kb

It also appears that the clock hands are continuously churning (the reason for the CPU reporting 23% ?). I initially checked CACHESTORE_OBJCP and CACHESTORE_SQLCP, but it also seems that there are about 250 clocks that seem to be continuously running (select count(*) from sys.dm_os_memory_cache_clock_hands where clock_status = 'running'). These generally are external hands (HAND_EXTERNAL).

The other slightly strange thing is that there are barely any plans in the cache (select count(*) from sys.dm_exec_cached_plans). The number ofplans seems to fluctuate between 2 and around 25, within the space of a few seconds. Most of these appear to be replication procedures.
I’ve checked the memory clerks and can see:
MEMORYCLERK_SOSNODE = 6563MB
MEMORYCLERK_SQLBUFFERPOOL = 3561 MB
USERSTORE_TOKENPERM = 47 MB
MEMORYCLERK_SQLGENERAL = 30 MB
USERSTORE_SCHEMAMGR = 26 MB
MEMORYCLERK_SQLCLR = 19 MB
Given the status of the clock hands and the lack of persistency of plans in the cache I’d assume that SQLServer believes it is under memory pressure (and is clearing plans?). Thisactivity could be based on the high percentage of stolen memory.
If that makes sense, then why is there so much stolen memory – or more to the point, on a very quiet system what is using this (or why hasn’t it been released - I don't think there are any open transactions etc.). Also can anyone tell me what MEMORYCLERK_SOSNODE is used for and if this is related to the stolen memory.
I think there are plans to do a server restart – but I’d value any opinions anyone has on this.

Many Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian (SSC) SSC-Addicted Points: 407 More actions · Answer 1

Hi,
A quick update. The server has been rebooted and things are looking more like you'd expect:

CPU - 0%
There are now thousands of plans in the cache

Target mem: 12,582,912 kb
Total mem: 12,582,912 kb

Stolen Memory: 1,768,736 kb
Database Cache Memory: 10,780,160 kb
Free Memory: 34,016 kb
Lock Memory: 297,768 kb
Maximum Workspace Memory: 9,455,520 kb
SQL Cache Memory: 10,352 kb

Clock hands are now stable

Memory Clerks:
MEMORYCLERK_SQLBUFFERPOOL    10,527 MB
CACHESTORE_OBJCP        301 MB
OBJECTSTORE_LOCK_MANAGER     290 MB
USERSTORE_TOKENPERM        67 MB
CACHESTORE_PHDR           66 MB
CACHESTORE_SQLCP        66 MB
MEMORYCLERK_SOSNODE        55 MB

I'd still be interested to understand more about who STOLEN memory is handled in SQLServer (specifically how it is released and managed) and also what is MEMORYCLERK_SOSNODE and whether is has any relation to the STOLEN memory.

Thanks,

Ian

Ian (SSC) SSC-Addicted Points: 407 More actions · Answer 2

Hi,

Thanks to Jonathan Kehayias for mentioning that there is a known issue with SQLServer 2014:

https://support.microsoft.com/en-us/help/3115789/fix-memory-leak-occurs-in-the-memobj-sosnode-type-object-when-you-run-a-query-that-contains-sys.database-permissions-in-sql-server-2014

I've managed to recreate the issue by repeatedly running a query that references SYS.DATABASE_PERMISSIONS and I could see a rise in the memory used by MEMORYCLERK_SOSNODE. After a certain point the server started to display the symptoms previously seen (before the reboot).

Thanks,
Ian

miamikk204 SSC Veteran Points: 201 More actions · Answer 3

Ian (SSC) - Tuesday, April 4, 2017 7:27 AM
Hi,
Thanks to Jonathan Kehayias for mentioning that there is a known issue with SQLServer 2014:
https://support.microsoft.com/en-us/help/3115789/fix-memory-leak-occurs-in-the-memobj-sosnode-type-object-when-you-run-a-query-that-contains-sys.database-permissions-in-sql-server-2014
I've managed to recreate the issue by repeatedly running a query that references SYS.DATABASE_PERMISSIONS and I could see a rise in the memory used by MEMORYCLERK_SOSNODE. After a certain point the server started to display the symptoms previously seen (before the reboot).
Thanks,
Ian

Hi Ian, can you advise what query did you run to recreate the issue ?

Ian (SSC) SSC-Addicted Points: 407 More actions · Answer 4

Hi,
I repeatedly performed a SELECT from sys.database_permissions. You could probably do this manually, but I used a loop (see below). From memory, I think I had to run it a few times before the symptoms appeared. Obviously, give this a quick test first and be careful where you run it 🙂
I hope this helps.
Cheers,
Ian
----------------------------------------------------------------------------
set nocount on
declare @b-2 int
declare @a int = 1
while @a < 5000
begin
select @b-2 = count(*) from sys.database_permissions

SELECT @b-2 = count(pe.permission_name)
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;

set @a = @a + 1
end