none
SQL Server Memory Questions We were too shy to ask

    Question

  • Hi,

    I have the following SQL Server Memory related questions. Shedding light on the same highly appreciated.

    1. The Maximum Memory Configuration applies only to BufferPool Memory Usage.

    2. BufferPool & Plan Cache Memory usages are different and Plan Cache Memory usage is not restricted by Maximum Memory Setting.

    3. I am using Process Explorer at on server on which the Maximum Memory configuration is set to 18 Gigs whereas the total SQL Server is showing as 19+ Gigs, the Physical Memory on this server is 24 Gigs. I understand there some non-bufferpool usages, Is the non-bufferpool usage could be the only content.

    4. What are the major memory usage blocks that is showing with Process explorer like, Bufferpool, Plan Cache, Non-bufferpool memory usages, is there any other memory usage items ?

    5.On a 2008 64-bit instance, Maximum Memory is configured with 18 Gigs and the Process Explorer is showing the memory usage as 19+ Gigs, As per task manager the memory usage is more than 95 % of the total memory 24 Gigs. To get rid of the alert, If I forcefully issue the following queries, I am able to get rid of the memory critical alerts, i.e using Maximum Memory configuration the maximum limit is reduced to 4 Gigs and increasing it again to 18 Gigs, is this an recommended way of doing ? If not, what could be the impact,

    EXEC  sp_configure‘max server memory (MB)’,4096;
    GO
    RECONFIGURE;
    GO

    EXEC  sp_configure‘max server memory (MB)’,18432;
    GO
    RECONFIGURE;
    GO

    6. I have issued DBCC FREEPROCCACHE on the SQL Server instance, and the memory usage on Process Explorer is not getting reduced. (Before issuing this query check that the total plan cache usage memory is more than 3 Gigs, the following query is used to check the plan cache memory usage :

    SELECT SUM(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
    FROM sys.dm_exec_cached_plans;


    Tuesday, April 08, 2014 7:03 AM

Answers

  • Hi,

    Below is answer

    1.Yes.

    2.Buffer pool includes all data cache and plan cache and other caches.Memory for all these caches which requires <8 KB are taken from buffer pool for page request >8KB outside buffer ppol memory is allocated using Win API

    Edit:More about plan cache

    3.PLease dont use process explorer or task manager to view SQL Server memory utilization below query will give you sql server memory utilization for SQL server 2008 and above

    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

    Remember max server memeory only controls buffor pool allocation ,there can be allocation from Windows API or direct memory allocation whichi will not be taken from Buffer pool.So if you have kept buffer pool to 24 G SQL server can take more than 24 G

    4.Yes there are many DBCC MEMORYSTATUS or below query will give you various clerks consuming memory

    select * from sys.dm_os_memory_clerks

    5.Again dont use process explorer.What you did was you reduced max server memory to 4 G and then increased to 18 G.SQL server using memory is not an issue unless you can show me out of memory error.If you have 24 G on system .Leave atleast 5 |G for OS and allocate rest to SQL server.This is assuming you dont have multiple instances and no SSIS and SSRS SSAS.Process explorer shows memory for Windows not SQL server.

    6.Simple advise dont use freeproccache.it will flush out proc cache and you will face issue with new queries they might become extremely slow.

    This Article will be helpful

    SQL Server Memory and Troubleshooting

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers




    Tuesday, April 08, 2014 8:10 AM
  • Shanky_621,

    Thanks for the reply.

    While referring my understanding is BufferPool memory does not include the Plan Cache. but you have mentioned that "Memory for all these caches are taken from buffer pool."

    Please help me by providing any reference for this if you have. Because I am unable to confirm this.

    Hi,

    You did not read about plan cache internals Link I posted ,do you.But I would like to add correction to my statement that.All 8 KB pages requested by Cache plan comes from buffer pool and any requests that require pages >8 K is done through Windows API.Still Buffer pool remains major supplier of ALL memory requests.Read this

    http://technet.microsoft.com/en-us/library/cc293624.aspx

    Buffer pool is major contributor of memory and almost all requests are satifies by this memory because generally data pages are 8 KB so buffel pool can satify that request.For continuous large memory allocations Windows PAI allocates memory.

    For second aprt of question .I dont believe monitoring tool about memoryutilization what it is giving you Windows server utilization.So please check on OS what other processes are consuming memory.I still say drop clean buffer is not required.Do you have Out of memory error in sql errorlog ?

    You still did not provide me complete information about queries I asked please read my first post and reply completely.

    EDIT: Use below link to test if SQL server is facing memory crunch

    How to test SQL Server is facing memory crunch


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, April 08, 2014 12:32 PM

All replies

  • Hi,

    Below is answer

    1.Yes.

    2.Buffer pool includes all data cache and plan cache and other caches.Memory for all these caches which requires <8 KB are taken from buffer pool for page request >8KB outside buffer ppol memory is allocated using Win API

    Edit:More about plan cache

    3.PLease dont use process explorer or task manager to view SQL Server memory utilization below query will give you sql server memory utilization for SQL server 2008 and above

    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

    Remember max server memeory only controls buffor pool allocation ,there can be allocation from Windows API or direct memory allocation whichi will not be taken from Buffer pool.So if you have kept buffer pool to 24 G SQL server can take more than 24 G

    4.Yes there are many DBCC MEMORYSTATUS or below query will give you various clerks consuming memory

    select * from sys.dm_os_memory_clerks

    5.Again dont use process explorer.What you did was you reduced max server memory to 4 G and then increased to 18 G.SQL server using memory is not an issue unless you can show me out of memory error.If you have 24 G on system .Leave atleast 5 |G for OS and allocate rest to SQL server.This is assuming you dont have multiple instances and no SSIS and SSRS SSAS.Process explorer shows memory for Windows not SQL server.

    6.Simple advise dont use freeproccache.it will flush out proc cache and you will face issue with new queries they might become extremely slow.

    This Article will be helpful

    SQL Server Memory and Troubleshooting

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers




    Tuesday, April 08, 2014 8:10 AM
  • Shanky_621,

    Thanks for the reply.

    While referring my understanding is BufferPool memory does not include the Plan Cache. but you have mentioned that "Memory for all these caches are taken from buffer pool."

    Please help me by providing any reference for this if you have. Because I am unable to confirm this.

    Secondly,

    I understand that DBCC FREEPROCCACHE is not a recommended option on production instance. Still due to memory issue, I am giving a try for this article by Glenn Berry.

    http://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/

    Third,

    We are receiving Memory usage critical alert for a monitoring application. The server details are as follows :

    Total Memory 24 Gigs, Max Memory is set to 18 Gigs. But over the period, the total memory usage of this instance is slowing increasing and the core memory consumer is SQL Server instance.

    At present we are not able to increase the memory for this server at the same time we need to get rid of the memory alert.

    So at least on a weekly basis, we are using DBCC FREEPROCCACHE & DBCC DBCC DROPCLEANBUFFERS

    But, after issuing this command also the total memory usage by SQL Server is not getting decreased unless we restart the instance.

    Instead of restarting the SQL Server instance, one of the developer is proposing this method, i.e decreasing the Max Server configuration and increase it.

    Being a DBA, I need to justify the pros and cons of this activity as ball is on my court now.

    Tuesday, April 08, 2014 11:03 AM
  • Shanky_621,

    Thanks for the reply.

    While referring my understanding is BufferPool memory does not include the Plan Cache. but you have mentioned that "Memory for all these caches are taken from buffer pool."

    Please help me by providing any reference for this if you have. Because I am unable to confirm this.

    Hi,

    You did not read about plan cache internals Link I posted ,do you.But I would like to add correction to my statement that.All 8 KB pages requested by Cache plan comes from buffer pool and any requests that require pages >8 K is done through Windows API.Still Buffer pool remains major supplier of ALL memory requests.Read this

    http://technet.microsoft.com/en-us/library/cc293624.aspx

    Buffer pool is major contributor of memory and almost all requests are satifies by this memory because generally data pages are 8 KB so buffel pool can satify that request.For continuous large memory allocations Windows PAI allocates memory.

    For second aprt of question .I dont believe monitoring tool about memoryutilization what it is giving you Windows server utilization.So please check on OS what other processes are consuming memory.I still say drop clean buffer is not required.Do you have Out of memory error in sql errorlog ?

    You still did not provide me complete information about queries I asked please read my first post and reply completely.

    EDIT: Use below link to test if SQL server is facing memory crunch

    How to test SQL Server is facing memory crunch


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Tuesday, April 08, 2014 12:32 PM