September 24, 2012 at 3:11 pm
Is this a valid query to tell the total amount of memory used by the sqlservr.exe process regardless of whether LPIM is enabled?
SELECT CAST((physical_memory_in_use_kb + locked_page_allocations_kb)
/ (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS MemoryUsedBySqlServerGB
FROM sys.dm_os_process_memory;
If not, how can that be figured from T-SQL?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 1:33 am
No, it's not. This value
locked_page_allocations_kb
is a part of
physical_memory_in_use_kb
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 2:46 am
One doubt.. Will above query include MemtoLeave memory also?
September 25, 2012 at 6:45 am
Perry Whittle (9/25/2012)
No, it's not. This value
locked_page_allocations_kb
is a part of
physical_memory_in_use_kb
I didn't think locked pages were part of the working set which is why Task Manager lies to us but maybe locked_page_allocations_kb is not what I am thinking it represents. I don't have a 2008 machine with LPIM enabled at the moment to try it out so all my instance have 0 for locked_page_allocations_kb.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 6:46 am
dbasql79 (9/25/2012)
One doubt.. Will above query include MemtoLeave memory also?
As far as I know MemToLeave should be part of the working set, so yes, if I am understanding the columns correctly. But if I understood them 100% I would not have posted 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 7:50 am
opc.three (9/25/2012)
I didn't think locked pages were part of the working set which is why Task Manager lies to us but maybe locked_page_allocations_kb is not what I am thinking it represents. I don't have a 2008 machine with LPIM enabled at the moment to try it out so all my instance have 0 for locked_page_allocations_kb.
AWE mapped memory is not part of the working set 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 8:33 am
Perry Whittle (9/25/2012)
opc.three (9/25/2012)
I didn't think locked pages were part of the working set which is why Task Manager lies to us but maybe locked_page_allocations_kb is not what I am thinking it represents. I don't have a 2008 machine with LPIM enabled at the moment to try it out so all my instance have 0 for locked_page_allocations_kb.AWE mapped memory is not part of the working set 😉
I realize that. That is my motivation for adding in the coulmn that implies it is the amount of memory allocated as a locked page, i.e. AWE memory.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 8:47 am
Perry Whittle (9/25/2012)
opc.three (9/25/2012)
I didn't think locked pages were part of the working set which is why Task Manager lies to us but maybe locked_page_allocations_kb is not what I am thinking it represents. I don't have a 2008 machine with LPIM enabled at the moment to try it out so all my instance have 0 for locked_page_allocations_kb.AWE mapped memory is not part of the working set 😉
This is how I arrived at the original query and question whether anyone could confirm. I think I was reading the 2012 docs though.
From 2012 docs
physical_memory_in_use_kb: Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs. Not nullable.
locked_page_allocations_kb: Specifies memory pages locked in memory. Not nullable.
No mention of AWE APIs.
Then in the 2008 R2 docs
physical_memory_in_use: Process working set in KB, as reported by operating system, plus tracked allocations done by using large page and AWE APIs.
locked_page_allocations_kb: Physical memory that is allocated by using AWE APIs.
Some things changed in 2012 with memory settings, specifically which memory counts against the 'max memory' setting, but there was a change to the docs here as well but is there really a difference in these column values? I'll have to test it out.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 9:11 am
opc.three (9/25/2012)
I realize that. That is my motivation for adding in the coulmn that implies it is the amount of memory allocated as a locked page, i.e. AWE memory.
The point is, it's not AWE memory, the memory is managed by the AWE APIs but it's not AWE mapped memory, is this 32 bit or 64 bit SQL Server you are using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2012 at 10:21 am
Perry Whittle (9/25/2012)
opc.three (9/25/2012)
I realize that. That is my motivation for adding in the coulmn that implies it is the amount of memory allocated as a locked page, i.e. AWE memory.The point is, it's not AWE memory, the memory is managed by the AWE APIs but it's not AWE mapped memory, is this 32 bit or 64 bit SQL Server you are using?
Well, On 32-bit it is AWE-mapped memory and on 64-bit it's memory-locked by allocating it using the AWE APIs. I do not think I am interested in that nuance though, either way it's locked memory so I was thinking it would show in the locked_page_allocations_kb regardless of the scenario.
This is partly for my own education and partly for having a query at the ready for helping others on these forums that have questions about the diff between Task Manager reporting sqlservr.exe memory use on the Processes and total memory available on the Performance Tab. I am looking for a good way to show, from T-SQL, how much memory is used by sqlsrvr.exe including locked memory allocations not shown in the Task Manager Processes Tab. If one query could do that on 32-bit and 64-bit the same that would be ideal.
It appears that (after reading the correct version of the docs) that the query for 2008/R2 can simply be:
SELECT CAST(physical_memory_in_use_kb
/ (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS MemoryUsedBySqlServerGB
FROM sys.dm_os_process_memory;
Agreed?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply