Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about sys.dm_os_process_memory Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 3:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1363733
Posted Tuesday, September 25, 2012 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 6,742, Visits: 14,381
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"
Post #1363829
Posted Tuesday, September 25, 2012 2:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
One doubt.. Will above query include MemtoLeave memory also?
Post #1363859
Posted Tuesday, September 25, 2012 6:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1364000
Posted Tuesday, September 25, 2012 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1364002
Posted Tuesday, September 25, 2012 7:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 6,742, Visits: 14,381
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"
Post #1364049
Posted Tuesday, September 25, 2012 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1364084
Posted Tuesday, September 25, 2012 8:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1364092
Posted Tuesday, September 25, 2012 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 6,742, Visits: 14,381
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"
Post #1364115
Posted Tuesday, September 25, 2012 10:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1364166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse