SQLServer memory

by

How to configure maximum server memory

Follow the steps below to configure max server memory and minimum server memory for SQL Server.

If you would like to derive the value for max server memory and minimum server memory for sql server   follow  http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

Method 1

1.In Object Explorer, right-click a server and select Properties.

2.Click on Memory .

3.Under Server properties –> Memory  enter the value that you want for Minimum server memory and Maximum server memory.

  • image

    •  

     

    Method 2

    sp_configure  ‘show advanced options’,1
    reconfigure with override
    sp_configure   ‘max server memory (MB)’,2000 — Specify the value here
    reconfigure with override

    sp_configure ‘min server memory (MB)’,100– Specify the value here
    reconfigure with override

    Advertisement
    by

    SQL Server assert in Location: purecall.cpp:51

    SQL Server assert in purecall.cpp:51

    BEGIN STACK DUMP:

    spid 231

    Location: purecall.cpp:51

    Expression: !”purecall”

    SPID: 200

    Process ID: 5125

    Description: Pure virtual function call

    Server Error: 17065, Severity: 16, State: 1.

    Server SQL Server Assertion: File: <purecall.cpp>, line = 51 Failed Assertion = ‘!”purecall”‘ Pure virtual function call. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

    Possible causes for above assert are

    1. Antivirus softwares which detours in sqlserver address space can inject their instruction in sqlserver modules and can cause this Ex. Sophos etc..

    Run select * from sys.dm_os_loaded_modules and check if there are DLL’d loaded from Antivirus (Company column will have the AV company name). If you see any antivrus exclude SQLServer from them.

    (or)

    Run lm command in the dump and see if there are any Antivirus DLL’s loaded in sqlserver process memory.

    2. If you don’t see any Antivirus dll then run windows memory diagnostic tool and check if there are any memory problems on your system( %windir%\system32\MdSched.exe).

    3. If there is no antivirus or memory errors follow the steps in http://mssqlwiki.com/2012/10/16/sql-server-exception_access_violation-and-sql-server-assertion/

    by

    Lock pages in memory is recommended or not

    One of SQL Server expert recently asked  below question in distribution list 
    1. LPIM is not recommended?
    2. LPIM is not required?
    Answer:
    When we have LPIM privilege for SQL Server startup account  AWE allocator API’s are used for memory allocation. Memory allocated using  AWE allocator API’s are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon àPrivate bytes or Perfmon àworking set.
    Advantage: SQL Server working set (BPOOL) cannot be paged by windows  even when there is system wide memory pressure.
    Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory. LPIM prevents only the BPOOL from paging, Non-Bpool components can still be paged and we have critical portions of SQL Server memory like thread stack, SQL Server Images/DLL’s  in Non-Bpool which can still be paged by OS.
    So many disadvantage…. But still why do we recommend LPIM in some places?
    In earlier versions of windows 2003 when there is system wide memory pressure windows memory manger would trim one-quarter of working set of all the process. Imagine If SQL Server is using 200GB of RAM and there is system wide memory pressure, Windows memory manager would move 50 GB of
    SQL Server working set to page file and we would end with performance problems. If LPIM is enabled OS cannot trim.
    Imagine there is a faulty application in the server and it leaks memory  fast , It might consume all the memory in the server and windows memory manager might trim all of SQL Server working set.
    Known issues in windows like the one in This link can cause windows memory manager to trim the working set of SQL Server process suddenly. Windows has a background process which keeps  writing the contents of working set to page file, so when there is paging only the dirty pages  needs to be moved to the page file others are already backed by back ground process, So paging would be very fast and SQL Server working set would be moved to page file in seconds before SQL Server responds to low memory resource notification from OS causing negative performance.  This link might throw more clarity.

    In systems with large amout of memory (Ex: 1 TB )we might get non yielding scheduler situations when allocating memory in conventional memory model. LPIM is only option is this case.
     
    LPIM can be used in servers in which it might take long time to identify the cause of the working set trim. It is always suggested to identify the cause of TRIM before choosing LPIM in first place.

    by

    Same value for min server memory and max server memory in SQL server

    SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

    SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage.

    How much it scales down?
    Till “Min server memory”  (If there is continous memory pressure on the system).
    What happens when you set Max server memory and min server memory to same value?
    Ans:SQL Server will never scale down its memory usage even when there is memory pressure system wide (Lowphysicalmemory notification  set at system level)

    What are the affects?
    Ans:If LPM is not enabled SQL Server’s working set will be paged. If LPM is enabled system will starve for memory  and non-bpool will be paged.

    Refer http://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/ for more details.

    Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

    Make sure you have all the fixes for working set trim refer http://mssqlwiki.com/sqlwiki/sql-performance/windows-2008-and-windows-2008-r2-known-issues-related-to-working-set-memory/