none
Memory allocation in sql server RRS feed

  • Question

  • Hi,

    We have sql server (enterprise 64 edition)infrastructure and it has 190 GB RAM.This server holds four servers(two analysis services and two database engine).

    We are getting memory pressure sometime so how to allocate memory among all 4 services without impact others.

    Cheers,



    • Edited by ESQL5 Saturday, January 10, 2015 7:45 PM
    • Moved by Kalman TothEditor Monday, January 12, 2015 1:27 PM Not database design
    Saturday, January 10, 2015 7:44 PM

All replies

    1. Do you have max server memory set for all 4 of them? For the 2 SQL instances, this setting only applies to the buffer pool part of the memory
    2. Which of the service is encountering memory pressure?
    3. For 2 SQL instances, Setup Perfmon and monitor Page Life Expectancy (PLE), Target Memory, Total Memory and Memory grants pending counters. See if PLE is near or above 75 times the SQL max memory (in GB) configured for a instance. 
    4. Even if you do find a instance that is encountering memory pressure, there is no need to just allocate more memory to it. Instead, first make sure to tweak your indexes (and add missing indexes) to mitigate memory pressure

    Satish Kartan www.sqlfood.com

    Saturday, January 10, 2015 8:47 PM
  • Do you mean the server has  four SQL Server instances  on the server? If so,  you need to set a MAX memory value for each instance, please read this article

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/


    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

    Sunday, January 11, 2015 7:45 AM
  • Hello ,

    I want to see it in a different way , Do have the following question's answer:


    • Memory device :Slow, cheap storage is it?
    • Is the Databases properly indexed ?
    • Can't CPUs build query plans fast enough?
    • Is the database programmatically tuned ?
    • Have you use AWE for Allocating Memory?
    • Is the memory min and max limit set up ?
    • Have you check the memory configuration ?

    Also use : SQL Server Resource Governor to identify each server's resource status .


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, January 12, 2015 5:23 AM
  • We are getting memory pressure sometime so how to allocate memory among all 4 services without impact others.

    Well how did you know its a memory pressure, any out of memory error you got ? There is no point in providing answer unless you tell Version and edition of SQL Server so what is output of

    select @@Version
    Memory management has changed starting from SQL Server 2012. You must post same question on different thread for SSAS they can tell you how to gauge memory requirement for SSAS If you tell me version of SQL server i can give you nice explanation how to configure max server memory for database engine


    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 Wiki Article

    MVP

    Monday, January 12, 2015 11:26 AM