5 fixed minor typos in code and text
source | link

There are other methods as well. Perhaps you can use two DMV'sDMVs. Please note that both will only work for SQL Server 2008 and above.

A non zero value for locked pages allocation KBlocked_page_allocations_kb would tell you that SQL Server account has Locked pages in memory privilege.

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And...and:

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Columncolumn Locked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

And...and:

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege.

NOTE:

If SQL Server service account is running with Local System  ( NTNT Authority\System) account, by Defaultdefault SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for standard editionStandard Edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQlSQL Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE:

If SQL Server service account is running with Local System( NT Authority\System) account, by Default SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for standard edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQl Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

There are other methods as well. Perhaps you can use two DMVs. Please note that both will only work for SQL Server 2008 and above.

A non zero value for locked_page_allocations_kb would tell you that SQL Server account has Locked pages in memory privilege.

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

...and:

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys.dm_os_process_memory

If column Locked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

...and:

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege.

NOTE:

If SQL Server service account is running with Local System  (NT Authority\System) account, by default SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for Standard Edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQL Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

4 added update
source | link

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KBLocked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUSDBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: 

If SQL Server service account is running with Local System( NT Authority\System) account, by Default SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for standard edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQl Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: If SQL Server service account is running with Local System( NT Authority\System) account by Default SQL Server will have Locked pages in memory privilege

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: 

If SQL Server service account is running with Local System( NT Authority\System) account, by Default SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for standard edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQl Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

3 added 25 characters in body
source | link

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id,  
osn.memory_node_id,  
osn.node_state_desc,  
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: If SQL Server service account is running with Local System( NT Authority\System) account by Default SQL Server will have Locked pages in memory privilege

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: If SQL Server service account is running with Local System( NT Authority\System) account by Default SQL Server will have Locked pages in memory privilege

There are other methods as well. Perhaps you can use two DMV's. Please note that both will only work for SQL Server 2008 and above

A non zero value for locked pages allocation KB would tell that SQL Server account has Locked pages in memory privilege

select osn.node_id,  
osn.memory_node_id,  
osn.node_state_desc,  
omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'

And

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

If Column Locked_pages_allocation_KB shows a value then also SQL Server service account has Locked pages in memory privilege.

And

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege

NOTE: If SQL Server service account is running with Local System( NT Authority\System) account by Default SQL Server will have Locked pages in memory privilege

2 added information
source | link
1
source | link