I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave
area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.
This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave
area:
SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0
The post also explains memtoleave
well.
Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave
area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave
area is enough to cause some of the errors you listed.
Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave
exhaustion.
I would focus mainly on what is starving the memtoleave
area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.
I would also consider whether the problem helps make a business case for migrating to a 64-bit version of SQL Server, as this avoids memtoleave
issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.
To start with you have SQL Server 2012 and memory architecture changed significantly in this version there is only one memory allocator (yes there is VAS allocator as well but I still would say there is only one 'Any Page' allocator) and this does Almost All memory allocations. Here is what BOL has to say. Please read this definition clearly
Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, Memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.
For your question
Total components which use memory in SQL can be found through below query,were each distinct clerk means a component.
From above definition please note that not all
memory allocations are tracked by clerks. But for practical purposes you can safely assume sys.dm_os_memory_clerks is place to look for memory breakup as used by various components.Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.
Please note clerks only track memory allocated to various components they never do allocation of SQL Server memory.
Buffer Pool plus query plans will be able to use maximum of 58 GB ram
Not only buffer pool and query plans but almost all components which require memory will now be catered from max server memory setting. Specially SQLCLR, memory to this is now allocated from max server memory setting previously in SQL Server 2008 R2 and before memory was allocated from outside max server memory setting.
Since sql server calculates number of locks during startup,will the memory used by locks fall into 6 GB category ?
No memory will be allocated by Max server memory setting(will fall under 58G category) not from outside this limit. I am not using word buffer pool
as from SQL Server 2012 buffer pool is only consumer not allocator of memory so its better to use max server memory
Suppose i have executed a query which requires more memory ,will this memory be allocated from 6 Gb or from buffer pool (58 Gb ) memory or will this be spilled to disk ?
Well it depends on what components require memory. Please read the definition on top if resource that requires memory is Thread stack, memory heap, Third Party linked server provider the memory would be allocated from outside max server memory limit otherwise from inside. When query requires more memory Lazywriter would start ageing out unused pages and internal threads would ask various memory consumers to free some memory. This would create new space in buffer pool and SQL Server can bring would bring new pages into the memory.
There are different memory clerks all shown above in screenshot,will they fall in 6 GB category ?
You have not added screenshot but when you query sys.dm_os_memory_clerks whatever clerks information come they all track memory allocated to various components and would fall under max server memory limit category (58 G limit category).
In 64 Bit systems,does Sql still calculate memtoleave option or with the exception of few memory clerks ,everything falls under 58 GB category ?
NO THERE IS NO MEMORYTOLEAVE
IN 64 BIT. Please read This Blogs.msdn article for more details.
Further largest consumer of these clerks would be buffer pool and query plans. If other clerks face memory pressure or need more memory,memory allocation will come through buffer pool clerk in terms of stolen pages if they pages flushed to disk to accomodate this request.
No it is not exactly like that. Yes in SQL Server 2012 as well largest consumer of memory is buffer pool. Stolen memory is memory utilized by SQL Server to do miscellaneous tasks NOT REALTED
to SQL Server database. These other Miscellaneous task might include sorting, has joins etc. So in case of memory pressure it might or might not take stolen memory depending on memory pressure.
Best Answer
I will quote from my answer given on This Thread.
Plan cache is not part of stolen memory.
AFAIK their is no query to tell what "actually" stolen memory consists and how much. You can run
DBCC MEMORYSTATUS()
command to see the value of stolen memory.