none
MS SQL Server 2008 R2 Performance - Reconfigure

    คำถาม

  • Hi Techies,

        First of all, thank you all for replying my question.

    Environment - Production:

         MS SQL Server 2008 R2 installed in Windows 2008 server 64 bit (Dedicated) (Installed memory 28GB). This DB is replicated to three more physical read only servers & this primary DB is connected to 8 web servers which are in load balancing.

    Current Scenario:

         After SQL server in action for some time (Variable) there is a shoot up of memory in the windows server. please find the screen shot below,

         

         I am very sure this is not because of memory leaks & it is cache stored.

    Resolution:

         I implemented the below code & scheduled a job which runs in a interval and flushes the unused memory. This in-turn brings down the memory to considerable GB. Please find the code below,

         ---------------------------------------------------------

         sp_configure 'show advanced options', 1;
         GO
         RECONFIGURE;
         GO
         sp_configure 'max server memory', 4096;
         GO
         RECONFIGURE;
         GO
         WaitFor Delay '00:00:45'
         GO
         sp_configure 'max server memory', 24576;
         GO
         RECONFIGURE;
         GO

         ---------------------------------------------------------

         This was been running successfully for past three years without any issues.

    Question:

        1) Is this right approach to do ?
        2) Pros & Cons
        3) Leaving cache at high value is normal ?
        4) Any other alternate solution ?

    With Best Regards,

    Karthik



    15 พฤษภาคม 2555 23:08

คำตอบ

  • Hi,

    Yes. The SQL buffer cache is using the memory therefore this is normal and ok. Are you seeing any performance issues? Is there a reason why you limit SQL to 4GB before manually raising this with the script? I usually set the setting and leave it at that as SQL server will slowly ramp up the memory use after a restart it does not grab the max memory straight away for the buffer cache.

    Is SQL the only software running on the server? The max memory setting only controls the SQL buffer cache. SQL will use more memory outside of this for operations therefore you want to make sure you have enough memory available outside the buffer cache for the operating system and other processes such as backup agents, etc. The last thing you want is for the OS to indicate it is under memory pressure as this will force SQL to back off and therefore begin paging things of of memory to disk in order to make memory available to the OS.

    I would suggest you monitor the system around your memory settings and I would recommend checking out Glenn Berry's DMV queries - http://sqlserverperformance.wordpress.com/2012/04/30/sql-server-2008-diagnostic-information-queries-may-2012/ 

    -- Good basic information about OS memory amounts and state
    SELECT total_physical_memory_kb, available_physical_memory_kb, 
           total_page_file_kb, available_page_file_kb, 
           system_memory_state_desc
    FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
    -- You want to see "Available physical memory is high"
    -- This indicates that you are not under external memory pressure
    -- SQL Server Process Address space info 
    --(shows whether locked pages is enabled, among other things)
    SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
           page_fault_count, memory_utilization_percentage, 
           available_commit_limit_kb, process_physical_memory_low, 
           process_virtual_memory_low
    FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
    -- You want to see 0 for process_physical_memory_low
    -- You want to see 0 for process_virtual_memory_low
    -- This indicates that you are not under internal memory pressure
    -- Page Life Expectancy (PLE) value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Buffer Manager%' -- Handles named instances
    AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
    -- PLE is a good measurement of memory pressure.
    -- Higher PLE is better. Watch the trend, not the absolute value.
    -- Memory Grants Outstanding value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Outstanding]                                                                                                      
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
    AND counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);
    -- Memory Grants Outstanding above zero for a sustained period is a very strong indicator of memory pressure
    -- Memory Grants Pending value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]                                                                                                       
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
    AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
    -- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • ทำเครื่องหมายเป็นคำตอบโดย Karthi-Sand2SQLServer 17 พฤษภาคม 2555 21:02
    16 พฤษภาคม 2555 5:01

ตอบทั้งหมด

  • Hi,

    Yes. The SQL buffer cache is using the memory therefore this is normal and ok. Are you seeing any performance issues? Is there a reason why you limit SQL to 4GB before manually raising this with the script? I usually set the setting and leave it at that as SQL server will slowly ramp up the memory use after a restart it does not grab the max memory straight away for the buffer cache.

    Is SQL the only software running on the server? The max memory setting only controls the SQL buffer cache. SQL will use more memory outside of this for operations therefore you want to make sure you have enough memory available outside the buffer cache for the operating system and other processes such as backup agents, etc. The last thing you want is for the OS to indicate it is under memory pressure as this will force SQL to back off and therefore begin paging things of of memory to disk in order to make memory available to the OS.

    I would suggest you monitor the system around your memory settings and I would recommend checking out Glenn Berry's DMV queries - http://sqlserverperformance.wordpress.com/2012/04/30/sql-server-2008-diagnostic-information-queries-may-2012/ 

    -- Good basic information about OS memory amounts and state
    SELECT total_physical_memory_kb, available_physical_memory_kb, 
           total_page_file_kb, available_page_file_kb, 
           system_memory_state_desc
    FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
    -- You want to see "Available physical memory is high"
    -- This indicates that you are not under external memory pressure
    -- SQL Server Process Address space info 
    --(shows whether locked pages is enabled, among other things)
    SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
           page_fault_count, memory_utilization_percentage, 
           available_commit_limit_kb, process_physical_memory_low, 
           process_virtual_memory_low
    FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
    -- You want to see 0 for process_physical_memory_low
    -- You want to see 0 for process_virtual_memory_low
    -- This indicates that you are not under internal memory pressure
    -- Page Life Expectancy (PLE) value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Buffer Manager%' -- Handles named instances
    AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
    -- PLE is a good measurement of memory pressure.
    -- Higher PLE is better. Watch the trend, not the absolute value.
    -- Memory Grants Outstanding value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Outstanding]                                                                                                      
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
    AND counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);
    -- Memory Grants Outstanding above zero for a sustained period is a very strong indicator of memory pressure
    -- Memory Grants Pending value for current instance
    SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]                                                                                                       
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
    AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
    -- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • ทำเครื่องหมายเป็นคำตอบโดย Karthi-Sand2SQLServer 17 พฤษภาคม 2555 21:02
    16 พฤษภาคม 2555 5:01
  • Hello Karthik,

    In addition to what Sean Massey has said, I would like to add some thoughts based on your questions:

    1. Is this the right approach?

    This is not the correct apprach. Effectively when this is done SQL Server must purge out data and cache, I'm betting there is a pretty big spike in disk activity when this happens and I bet a corresponding jump in lazywriter activity is also present (it's just a guess, but it is based on flushing out memory). That is going to put a load on the IO subsystem not only when the data is flushed to disk but also when it is read back in, cache plans are outed and need to be compiled which is a CPU hit as well. The only reason I point this out is because of the already active instance which looks like it hosts something if it has 8 load balanced webservers in front of it and is replicating to 3 other servers.

    Instead, take into account any other processes or applications (hopefully none, but this includes RDP sessions, etc) and the OS. It is a dedicated SQL Server and from your screen shot above, there is 1.9 GB of FREE memory, I don't see the memory settings being an issue and would probably bump the max server memory up another 500 MB a couple times until the amoutn of free MB is around 500 MB. Are you starting to have problems when SQL Server uses all of the memory assigned to it?

    2. Pros and Cons?

    Pros - None that I can think of other than some alerting or monitoring tools stop alerting, but really an exception should be made for this server in that case. If you are having problems with SQL Server when this happens a pro would also be that the server is usable and stops exhibiting issues.

    Cons - IO Subsystem thrashing (loading and unloading of memory), CPU usage when the plan is compiled, root cause not being addressed but symptoms masked (if there is an issue other than the server is using available memory which is a good thing).

    3. Leaving cache at a high value is normal?

    Yes, it's cache. As long as you have > 300 MB or so of free memory for the server AND there are no stability or performance issue with the server, I don't see this as a problem.

    4. Any other alternate solution?

    Hard to say, we don't know what the problem is. If the problem is the server is using available memory, I don't actually see that as a problem - it's a good thing.

    -Sean


    Sean Gallardy, MCC | Blog

    16 พฤษภาคม 2555 14:33
    ผู้ตอบ
  • Sean,

        Thank you very much for your reply, I got more out of it. Thanks for your queries.

        Please find the answers below for the questions you asked,
        1) Are you seeing any performance issues? 
            We were having some performance issues intially then we fine tuned the application. I am not confident enough to remove this query from production environment. That's the reason I started this thread to get more info, in other words being pro-active.

        2) Is there a reason why you limit SQL to 4GB before manually raising this with the script?
            Please find the link "http://stackoverflow.com/questions/856575/sql-server-2008-takes-up-a-lot-of-memory", I took this as a sample code and implemented as recuring job. My aim is to reduce the memory usage (being pro-active). Even though it is declared as 4 GB, it brings down gradually for 45 sec i.e. it brings down usage upto 16 GB. Once again we set back to 25GB & it build again.

        3) Is SQL the only software running on the server?
            Yes, it is dedicated production MS-SQL server 2008 R2 server installed.

        Please find the screen shot below of the memory grants pending, it is "0". Please confirm whether it is good ? 
        The comments said "-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure", but not sure about "0"

        

        Finally, having this excellent queries aside, please let me monitor SQL server performance during peak usage of DB.
        Thanks again.

    Regards,
    Karthik


    Karthik B SCPM, PMP

    16 พฤษภาคม 2555 22:25
  • Sean,

       Thank you very much for pointing out by questions i asked, I appreciate. Your explanation is so informative.
       I have replied to Sean Massey in my last post, please refer if you may want some information from me.

       Only one question to you is, Bringing memory down & again allowing it to build is cosidered as "Partial Restart of SQL Server" ? Because even SQL server restart does the same thing. Also I don't see any spike in disk activity. This query allocate's the memory to 4gb & starts fluhsing the cache. Yes you are right, no other processes or applications are open.

       "Are you starting to have problems when SQL Server uses all of the memory assigned to it?"
       - We are not this far as we don't allow server to occupy its entire memory. We also have automated email in place, if in case it shoots up.

       Your ideas or thoughts on this link "http://stackoverflow.com/questions/856575/sql-server-2008-takes-up-a-lot-of-memory" is highly appreciated. This source made me to do this work around 3 years ago.

       Thanks again.

    Regards,
    Karthik


    Karthik B SCPM, PMP

    16 พฤษภาคม 2555 22:51
  • I read the Stack Overflow post and why I see what they are getting at I don't believe this is a good solution. If the OS is under memory pressure and needs to reclaim memory from SQL in most cases I would lower the SQL buffer max memory setting so that the OS does not force SQL to start dumping pages out of memory to disk.

    Your perf counter Memory\Available MBytes should not drop below at least 300MB. I usually want this to be higher than that for safety reasons however if you see that this is stable over an extended period you can then slowly increase the max memory and monitor this counter until you are happy you are not "wasting" memory resources.

    SQL will automatically flag pages that it determines are the 1st ones to write out from memory and it does a pretty good job up until the OS forces SQL to hand back memory. At this point as Sean G says you can start seeing a lot of I/O and disk thrashing as SQL is forced to read pages into the buffer and then dump them back out to disk and your PLE (Page Life Expectency) drops through the floor.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    16 พฤษภาคม 2555 23:03
  • Hi,

    If have lots of ad-hoc queries, this may be worth testing to see if it helps with plan cache bloat http://msdn.microsoft.com/en-us/library/cc645587(v=sql.105).aspx

    17 พฤษภาคม 2555 7:36
  • I modified your SQL for easier use (Problem values are flagged in seperate column). It may be helpful to those who do this as one of many job functions.

    Regards,

    Brad

    -- Good basic information about OS memory amounts and state


    SELECT

    total_physical_memory_kb, available_physical_memory_kb,

           total_page_file_kb

    , available_page_file_kb,

           system_memory_state_desc


    FROM

    sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);


    -- You want to see "Available physical memory is high"

    -- This indicates that you are not under external memory pressure

    -- SQL Server Process Address space info

    --(shows whether locked pages is enabled, among other things)


    SELECT

    physical_memory_in_use_kb,locked_page_allocations_kb,

           page_fault_count

    , memory_utilization_percentage,

           available_commit_limit_kb

    , process_physical_memory_low, process_virtual_memory_low,


          

    CASE  WHEN (process_physical_memory_low = '0' AND process_virtual_memory_low = '0') THEN 'NO' ELSE 'YES' END Memory_Pressure


    FROM

    sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);


    -- You want to see 0 for process_physical_memory_low

    -- You want to see 0 for process_virtual_memory_low

    -- This indicates that you are not under internal memory pressure

    -- Page Life Expectancy (PLE) value for current instance


    SELECT

    @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Page Life Expectancy]


    FROM

    sys.dm_os_performance_counters WITH (NOLOCK)


    WHERE

    [object_name] LIKE N'%Buffer Manager%' -- Handles named instances


    AND

    counter_name = N'Page life expectancy' OPTION (RECOMPILE);


    -- PLE is a good measurement of memory pressure.

    -- Higher PLE is better. Watch the trend, not the absolute value.

    -- Memory Grants Outstanding value for current instance


    SELECT

    @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Outstanding],  

    CASE cntr_value WHEN 0 THEN 'NO' ELSE 'YES' END Memory_Pressure                                                                                                     


    FROM

    sys.dm_os_performance_counters WITH (NOLOCK)


    WHERE

    [object_name] LIKE N'%Memory Manager%' -- Handles named instances


    AND

    counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);


    -- Memory Grants Outstanding above zero for a sustained period is a very strong indicator of memory pressure

    -- Memory Grants Pending value for current instance


    SELECT

    @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending],   

    CASE WHEN cntr_value > 0 THEN 'YES' ELSE 'NO' END Memory_Pressure                                                                                                  


    FROM

    sys.dm_os_performance_counters WITH (NOLOCK)


    WHERE

    [object_name] LIKE N'%Memory Manager%' -- Handles named instances


    AND

    counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);


    -- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure

    7 กุมภาพันธ์ 2557 18:07