InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 03:49:12 GMT20http://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]Perry Whittle (9/25/2012)[/b][hr][quote][b]opc.three (9/25/2012)[/b][hr]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.[/quote]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?[/quote]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:[code="sql"]SELECT CAST(physical_memory_in_use_kb / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS MemoryUsedBySqlServerGBFROM sys.dm_os_process_memory;[/code]Agreed?Tue, 25 Sep 2012 10:21:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]opc.three (9/25/2012)[/b][hr]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.[/quote]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?Tue, 25 Sep 2012 09:11:54 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]Perry Whittle (9/25/2012)[/b][hr][quote][b]opc.three (9/25/2012)[/b][hr]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.[/quote]AWE mapped memory is not part of the working set ;-)[/quote]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 [u][url=http://msdn.microsoft.com/en-us/library/bb510747(v=sql.110).aspx]2012 docs[/url][/u][quote]physical_memory_in_use_kb: Indicates the [b]process working set[/b] 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.[/quote]No mention of AWE APIs.Then in the [u][url=http://msdn.microsoft.com/en-us/library/bb510747(v=sql.105).aspx]2008 R2 docs[/url][/u][quote]physical_memory_in_use: [b]Process working set[/b] in KB, as reported by operating system, plus tracked allocations done by using large page [b]and AWE APIs[/b].locked_page_allocations_kb: Physical memory that is allocated by using AWE APIs.[/quote]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.Tue, 25 Sep 2012 08:47:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]Perry Whittle (9/25/2012)[/b][hr][quote][b]opc.three (9/25/2012)[/b][hr]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.[/quote]AWE mapped memory is not part of the working set ;-)[/quote]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.Tue, 25 Sep 2012 08:33:28 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]opc.three (9/25/2012)[/b][hr]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.[/quote]AWE mapped memory is not part of the working set ;-)Tue, 25 Sep 2012 07:50:35 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]dbasql79 (9/25/2012)[/b][hr]One doubt.. Will above query include MemtoLeave memory also?[/quote]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 :-DTue, 25 Sep 2012 06:46:56 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspx[quote][b]Perry Whittle (9/25/2012)[/b][hr]No, it's not. This value[code="SQL"]locked_page_allocations_kb[/code]is a part of[code="SQL"]physical_memory_in_use_kb[/code][/quote]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.Tue, 25 Sep 2012 06:45:14 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspxOne doubt.. Will above query include MemtoLeave memory also?Tue, 25 Sep 2012 02:46:15 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspxNo, it's not. This value[code="SQL"]locked_page_allocations_kb[/code]is a part of[code="SQL"]physical_memory_in_use_kb[/code]Tue, 25 Sep 2012 01:33:48 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1363733-1550-1.aspxIs this a valid query to tell the total amount of memory used by the sqlservr.exe process regardless of whether LPIM is enabled?[code="sql"]SELECT CAST((physical_memory_in_use_kb + locked_page_allocations_kb) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS MemoryUsedBySqlServerGBFROM sys.dm_os_process_memory;[/code]If not, how can that be figured from T-SQL?Mon, 24 Sep 2012 15:11:56 GMTopc.three