8

I get error from System Center Operations Manager (SCOM).

How to solve this error?

SQL DB 2012 Engine Stolen Server Memory is too high.

I run query and get this counters:

╔═══════════════════════════════╦═════════════╗
║ Stolen Server Memory (MB)     ║ 7354.773437 ║
║ Lock Memory (MB)              ║ 106.195312  ║
║ Free Memory (MB)              ║ 64.632812   ║
║ Connection Memory (MB)        ║ 24.203125   ║
║ Log Pool Memory (MB)          ║ 14.085937   ║
║ Optimizer Memory (MB)         ║ 2.351562    ║
║ Granted Workspace Memory (MB) ║ 1.296875    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
╚═══════════════════════════════╩═════════════╝
10

How many times do you see such messages? If it is once or twice it can be ignored.

'Stolen memory' as described in this older support article and How to use the DBCC MEMORYSTATUS is:

Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

The memory is usually taken from Buffer Pool. If you run DBCC MEMORYSTATUS and the output shows you high Stolen Pages, this means that some process is stealing memory from buffer pool more that what is necessary and you need to find that process.

It's quite likely that there was some operation running which performed a huge sort operation and at same time SQL Server was heavily consuming memory thus leading to this message. Unless you are facing some OOM issue this message would be more considered as a warning.

-2

Saw this from Google search as we're facing similar issue (Stolen memory is ~50% of total memory) but we cannot

Btw, I found below code

select type, name, sum((pages_kb*1024)/8192) as stolen_pages
from sys.dm_os_memory_clerks
where pages_kb > 0
group by type, name
order by stolen_pages desc;

type    name    stolen_pages
MEMORYCLERK_SQLBUFFERPOOL   Default 1710360

I modified it more to show % of Stolen as Total memory

SELECT Now = GETDATE()
    ,StolenMemory = (
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE [counter_name] IN ('Stolen Server Memory (KB)')
        )
    ,StolenMemoryPercent = 100.0 * (
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE [counter_name] IN ('Stolen Server Memory (KB)')
        ) / (
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE [counter_name] IN ('Total Server Memory (KB)')
        )

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.