Post reply

How to set memory if have multiple sql instances on same box?

  • September 11, 2017 at 12:24 pm

    #339065

    Hi All,

    Question on SQL Server Memory Configuration. Not looking for in-depth capacity planning but looking for high level answers.
    Suppose I had to install SQL Server, SSAS , SSIS, SSRS or be it install 3 separate SQL 2014 Instances on a single server which has 160GB physical RAM, then how would I configure memory for all the 3 instances.
    Which instance I need to give more or less memory. What are things to be considered to properly configure memory. I am looking for high level things/factors to be considered while distributing memory among multiple instance.

    Thanks,

    Sam

  • September 11, 2017 at 2:08 pm

    #1959022

    This is another "depends" question.  Is each instance treated as equally important?  Are the database sizes and expected I/O comparable for each instance?  How critical is the application whose back-end database is living on the instance?   If the database server is given 160 gb, for example,  I would probably divide that number by 3,  as a start, then take some away for the "extra" services and OS, then prioritize the amounts you set the Max Server Memory to in the properties of the instance.  If the extra services are going to be running on the same server, you will want to account for each one.  SSRS and SSAS each use its own memory from the server.  Both are configurable, but it is not straightforward, so, the defaults are usually taken.  The OS will need some memory also so you don't tax the OS.  People used to recommend 4 - 6gb left for the OS. Once the MaxMems are set and in place on each instance, once in production, you can adjust as needed based on how much memory each instance is actually using as the MaxMem setting is dynamic and does not require a restart of the instance to take affect.  Two performance counters some people use for memory usage is the BCHR and PLE and adjust memory accordingly. You could start out with 40gb per instance and see how the performance is.  This would leave 40Gb for the OS and the extra services to share.

  • September 12, 2017 at 6:29 am

    #1959078

    Thanks a lot for the valuable information RVSC48. Many thanks.

  • September 12, 2017 at 12:56 pm

    #1959171

    Its very important to know the buffer usage trends of each database for appropriate allocation from the total. Can use this query below to get the current stats .

    DECLARE @total_buffer INT;
    SELECT @total_buffer = cntr_value   FROM sys.dm_os_performance_counters
    WHERE RTRIM([object_name]) LIKE '%Buffer Manager'   AND counter_name = 'Total Pages';
    ;WITH src AS(   SELECT        database_id, db_buffer_pages = COUNT_BIG(*)
    FROM sys.dm_os_buffer_descriptors       --WHERE database_id BETWEEN 5 AND 32766      
    GROUP BY database_id)SELECT   [db_name] = CASE [database_id] WHEN 32767        THEN 'Resource DB'        ELSE DB_NAME([database_id]) END,   db_buffer_pages,   db_buffer_MB = db_buffer_pages / 128,   db_buffer_percent = CONVERT(DECIMAL(6,3),        db_buffer_pages * 100.0 / @total_buffer)
    FROM src
    ORDER BY db_buffer_MB DESC;

  • September 12, 2017 at 11:53 pm

    #1959201

    Thanks Arsh.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply