locked
SQL Server Memory RRS feed

  • Question

  • Hi,

    We have recently enabled AWE on SQL Server 2008 R2 (32-bit).  The Max Server Memory is set to use 12GB.  Is there any way to verify if this RAM is being visible at SQL end.  I've used available_physical_memory column from sys.dm_os_sys_memory to verify this but I can't understand if the result value is at SQL end or at OS level.  I've also added SQL Server: Memory  TotalServerMemory (KB) and TargetServerMemory(KB).  These are still running but believe these would only reflect after they are run for 6-8hrs during business.  Any quick method to verify the change done to AWE?

    Thanks,

    Sanjeev

    Thursday, September 4, 2014 3:28 AM

Answers

  • Hi,

    First please note that SQL Server memory utilization is dynamic its not necessary that if you have set max server memory to 12 GB buffer pool will immediately utilize all. It will utilize as per needed. So  rest assured, if you have capable windows system which can see more than 4 G of RAM and you have properly configured max server memory to use 12 G it will use it when required.

    Also note that since you have 32 bit system AWE memory will only be utilized by data and index pages.

    Below query would let you see total memory utilized by SQL Server taken from Here

    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

    Memory_usedby_SQLServer_MB is total memory utilized by SQL Server.

    If you really want to check whether you can throttle memory up-to 12 G you need to create a big dummy table start inserting huge records and then same time run many select * statements without where clause you would notice SQL Server memory increasing. Needless to say this would create load on system so be cautious.

    I also believe that in your OS can see more than 4 G of RAM otherwise there is no need of AWE.



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles


    Thursday, September 4, 2014 9:43 AM

All replies

  • Hi Sanjeev,

    Try using Performance Monitor to achieve this

    Get the counter value Target server Memory from SQL Server:Memory Manager

    This is the value that you want to look at to verify the RAM availabale to SQL Server

    Hope this helps

    More details in this linkhttp://msdn.microsoft.com/en-us/library/ms190924.aspx

    Bhanu



    • Edited by bhanu_nz Thursday, September 4, 2014 4:05 AM
    Thursday, September 4, 2014 4:03 AM
  • --sql server uses the memory
    select 
    CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
    bpool_committed,bpool_commit_target 
      from 
    sys.dm_os_sys_info

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 4, 2014 7:04 AM
  • Hi,

    First please note that SQL Server memory utilization is dynamic its not necessary that if you have set max server memory to 12 GB buffer pool will immediately utilize all. It will utilize as per needed. So  rest assured, if you have capable windows system which can see more than 4 G of RAM and you have properly configured max server memory to use 12 G it will use it when required.

    Also note that since you have 32 bit system AWE memory will only be utilized by data and index pages.

    Below query would let you see total memory utilized by SQL Server taken from Here

    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

    Memory_usedby_SQLServer_MB is total memory utilized by SQL Server.

    If you really want to check whether you can throttle memory up-to 12 G you need to create a big dummy table start inserting huge records and then same time run many select * statements without where clause you would notice SQL Server memory increasing. Needless to say this would create load on system so be cautious.

    I also believe that in your OS can see more than 4 G of RAM otherwise there is no need of AWE.



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles


    Thursday, September 4, 2014 9:43 AM